Data Definition – DDL (CREATE DATABASE, CREATE TABLE…) 1. Create the SQL stateme

Data Definition – DDL (CREATE DATABASE, CREATE TABLE…)
1. Create the SQL statements that construct (CREATE) a database named “DOCTIME” and all
of the tables (entities and attributes) using the MySQL database language syntax from the attached ERD.
2. Don’t foreget to decide the data type used for each attribute. You should use common sense
judgement when defining the data type. If the value uses any kind of text or character values, use
the VARCHAR() data type. When using the VARCHAR data type make sure you define the
maximum length potential for each value. For example: If you think the value will be on average
10 characters, may reach a maximum of 12, then use VARCHAR(12).
3. Remember, Primary Key fields require the NOT NULL property.
4. Do not worry about the Foreign Key or UNIQUE properties for this exercise.

Assignment 4A: Lab – Script (Schedule Date, Order Number, etc.) Create a script

Assignment
4A: Lab – Script (Schedule Date, Order Number, etc.)
Create
a script that returns complete date, Schedule date, and Order Number from
the Order table.
Create
a column that adds five days to the complete date. Call the column
‘Call Date’
Alias
the Order table
Create
a column that shows the amount of time between the schedule date and
complete date
Order
By Schedule Date
Show
your script and Results
Create
a script that returns Current Offer and charge from the Service table
Count
the number of each offer
Show
your script and results
Create
a script that pulls Course number and course name from the Course table
Only
pull courses that have the word ‘the’ in their description
Before
running the script – how many records do you think will be returned
Change
the Course number column to say ‘Course #’
Change
the Name field to say ‘Course Description’
Show
your pre-guess, script, and the results
To submit assignment, put the information above in a Word
document, including a copy of the actual SQL script required for each of the
three items. Load Word document with all content into the assignment box
for instructor review and grading.
Assignment
5A: Lab – Script (Customer, Order)
1) Create a script that pulls all the
columns for the Customer and Order tables
a) With the Customer as the primary table,
do a right join with the Order table
b) Before running the script – how many
records do you think will be returned?
c) Show your pre-guess, script, and the
results
2) Create a script that pulls Customer
first name, last name, current offer, and charge amount
a) For all the customers with a CAMP_CODE
of ‘BH’ add $.50 to their charge amount
b) For all the customers with a CAMP_CODE
of ‘CD’ add $.75 to their charge amount
c) For all the customers with a CAMP_CODE
of ‘PV’ add $1.00 to their charge amount
d) For all the customers with a CAMP_CODE
of ‘VB’ add $.25 to their charge amount
e) Create a new field called ‘Rate
Increase’ to show the new amounts.
f) Show your script and results
3) Create a script that pulls Student
First Name, Last Name, course term and name they are associated with, and any
grades they might have.
a) Concatenate the first and last name to
appear last name first and first name last with a coma between.
b) For any grades above 3.0 add .25.
For any grade below 3.0 subtract .25
c) Show your script and results
Note: I have uploaded all the document needed to run the scripts. Please also use SQLite application.

* What is implied? * Why are we talking about this in a database class? * Exampl

* What is implied?
* Why are we talking about this in a database class?
* Examples from history?
* Examples from contemporary society?
notes from class :
System is established to gain efficiency/output/input seeking efficiency
Goal: continuously
Common element: data
Development: farming, animal husbandry, specialization of labor, industrial revolution…assembly line…interne
Learning = Competitive Advantage:
Allows for specialization of labor
Emergence of experts
Innovation and process optimization
People become interchangeable resource
Organizations that “learn” will out-perform other organizations that do not
Jun 22, 2022 Lecture 2
Review: Organize or Die – mantra
Scrum is part of organize or die
Hierarchical →
Basic SQL:
Select
From
Where
Order by
Group by
SDLC:
Role of DBMS:
Back-end of N-tier Application
Holds data securely
Presents answers to queries = information
Allows for learning/gaining knowledge
History of database theory:
– File cabinet database → not learning
– Hierarchical database & Network database → complications (redundant data, less efficient system maintenance); corrupted
Biggest difference between is primary key and foreign key
– Relational data model: foreign keys. Less expensive to implement & maintain, less dependent on experts (first person articulated retinol database theory Edgar Codd)
– Easy to understand, able to improve and optimize business processes, easy to obtain information quickly
– Efficiency in data storage and implementation, scalability
No domain violation, Row dependency
Jun 23, 2022 Lecture 3
3-tier: front (presentation), middle (complex, more than 1 layer, 3 tiers by itself), back
SDLC: Systems Development Lifecycle
Planning → Analysis → Design → Build → Maintain
moF – Framework
ITIL – Framework
SDLC – Framework
Waterfall – construction
Lean – created by Toyota (Japanese) revolutionary of making cars (manufacturing)
Agile/scrum – software
ERD → Entity Relationship Diagram, conceptual, logical
Conceptual:
*interview people/research, investigate industry/business organization – existing data, observe processes
→ brainstorming
→ conceptual data diagram
Conceptual relation (row = tuple) = Logical entity (row= record) = Physical table
Column = attribute
Logical Design Phase:
Refining conceptual
Create ERD → ‘blueprint’
Normalization process
Validate transactions
pk/fk

Why are the terms ”field” and ”record” incorrect when referring to column an

Why are the terms ”field” and ”record”
incorrect when referring to column and row
2.
Why is the term ”NULL value” incorrect
3.
How many
orders where made from the Sales.Orders table
4.
Get the
Shippeddate,the count of Numorders,the count of Shippeddate,the minimum
shippeddate as the first ship date,the maximum shippddate as the Latestshipdate
(JUST WRITE QUERY)
5.
Get the top
3 order details of Total Sales where total sales is defined as the product
of the Unitprice and quantity (JUST WRITE QUERY)
6.
Get the
Ship ID, The total freight greater than 2000 from the Sales.orders table (JUST WRITE QUERY)
7.
Get the
hire country,hiredate ,the Number of employees hired after 20030101 from the
HR.Employees Table (JUST WRITE QUERY)
8.
Write a
query to return orders that were not shipped yet. Such orders have a NULL in
the shippeddate column.
9.
You are
requested to return all orders that were placed between February 11,2008 and
February 12,2008 (JUST WRITE QUERY)
10.
If I want
to return information about employees from Washington State in the United
States, sorted by city and empid (JUST WRITE QUERY)
11.
Write a
query that returns the orders for customer 77. Such that the rows will be
sorted by shipperid. To avoid tiebreakers perform secondary ordering by shipped
date and orderid, descending respectively (JUST
WRITE QUERY)

Data Definition – DDL (CREATE DATABASE, CREATE TABLE…) 1. Create the SQL stateme

Data Definition – DDL (CREATE DATABASE, CREATE TABLE…)
1. Create the SQL statements that construct (CREATE) a database named “DOCTIME” and all
of the tables (entities and attributes) using the MySQL database language syntax from the attached ERD.
2. Don’t foreget to decide the data type used for each attribute. You should use common sense
judgement when defining the data type. If the value uses any kind of text or character values, use
the VARCHAR() data type. When using the VARCHAR data type make sure you define the
maximum length potential for each value. For example: If you think the value will be on average
10 characters, may reach a maximum of 12, then use VARCHAR(12).
3. Remember, Primary Key fields require the NOT NULL property.
4. Do not worry about the Foreign Key or UNIQUE properties for this exercise.

Why are the terms ”field” and ”record” incorrect when referring to column an

Why are the terms ”field” and ”record”
incorrect when referring to column and row
2.
Why is the term ”NULL value” incorrect
3.
How many
orders where made from the Sales.Orders table
4.
Get the
Shippeddate,the count of Numorders,the count of Shippeddate,the minimum
shippeddate as the first ship date,the maximum shippddate as the Latestshipdate
(JUST WRITE QUERY)
5.
Get the top
3 order details of Total Sales where total sales is defined as the product
of the Unitprice and quantity (JUST WRITE QUERY)
6.
Get the
Ship ID, The total freight greater than 2000 from the Sales.orders table (JUST WRITE QUERY)
7.
Get the
hire country,hiredate ,the Number of employees hired after 20030101 from the
HR.Employees Table (JUST WRITE QUERY)
8.
Write a
query to return orders that were not shipped yet. Such orders have a NULL in
the shippeddate column.
9.
You are
requested to return all orders that were placed between February 11,2008 and
February 12,2008 (JUST WRITE QUERY)
10.
If I want
to return information about employees from Washington State in the United
States, sorted by city and empid (JUST WRITE QUERY)
11.
Write a
query that returns the orders for customer 77. Such that the rows will be
sorted by shipperid. To avoid tiebreakers perform secondary ordering by shipped
date and orderid, descending respectively (JUST
WRITE QUERY)