CMIS 320 UMGC Creating Physical Database from A Design Created Project

CMIS 320 Project 3
In this assignment, you will create the physical database from your design created in Project 2.
Project 2 was a logical model of the Mom & Pop database. We need to transition the logical
model into a physical implementation. As noted in the course content, a physical implementation
(or sometimes known as a physical model) is the database created on a system that we (or the
users) will use. To this end, we use SQL to create the database.
SQL is standard between many relational database types and is a part of Oracle, as noted in our
readings this week. Also, SQL had a few different ‘subsets’ of language commands, which includes
Data Definition Language (DDL) and Data Manipulation Language (DML), as two of the primary
SQL language subsets (there are a few other language subsets).
To create the database in a database tool like Oracle, we use SQL’s Data Definition Language
(DDL), which allows us to tell the database how the tables (entities) and the associated fields
within the tables (attributes) are described.
Step One: CREATE THE SQL DDL FOR YOUR DATABASE
For your logical database model created in Project 2, create the SQL DDL commands that will be
submitted to Oracle. Oracle will create the data structures to hold your data.
EXAMPLE: From our reading – Creating a table
•
•
•
•
•
•
•
•
•
•
Describe the layout of each table in the database.
Use CREATE TABLE command.
TABLE is followed by the table name.
Follow this with the names and data types of the columns in the table.
Data types define type and size of data.
Table and column name restrictions.
Names cannot exceed 30 characters.
Must start with a letter.
Can contain letters, numbers, and underscores (_)
Cannot contain spaces.
Figure 1: Create table REP using DDL
Consider a look at the course content this week. In addition, this resource may be helpful:
•
Introduction to Oracle CREATE TABLE:
https://www.oracletutorial.com/oracle- basics/oracle-create-table/
If an error is encountered or you wish to re-create the table, you will first need to DROP the
existing table. Dropping a table is done when:
•
Correcting errors by dropping (deleting) a table and starting over.
•
Useful when a table is created before errors are discovered.
•
Command is followed by the table to be dropped and a semicolon.
•
Any data in table also deleted.
Be sure to SAVE your work. Saving your work with a .sql extension will:
•
Allow you to use commands again without retyping.
•
Save commands in a script file or script – Text file with .sql extension.
Step Two: LOAD DATA INTO YOUR DATABASE
Following the creation of the database structure (the tables and fields) using DDL, we’ll need to
load data into the structures (e.g., populate data into the tables). Populating the Mom and Pop
Video Store database is done by Data Manipulation Language (DML) SQL commands.
As an example, let’s create a table not related to our project. We will use INSERT to add the first
few records to it. The example code below will create a table named users that has 5 columns.
We’ll have an id column that will be the PRIMARY KEY (the column that will always have unique
values and allow us to uniquely identify a row), and then the name, age, state, and email columns.
The SQL DDL is:
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT,
age INTEGER, state TEXT, email TEXT);
Load data into the ‘users’ table by using the SQL DML INSERT command. We will add the user Paul
with an id of 1 , an age of 24, from the state of Michigan, and with an email address of
paul@example.com using the query below:
INSERT INTO users
VALUES (1, “Paul”, 24, “Michigan”, “paul@example.com”);
The result will look like the following:
id(PK)
1
name
Paul
age
24
state
Michigan
email
paul@example.com
Add a few more records using the INSERT command:
INSERT INTO users (name, state)
VALUES (“Molly”, “New Jersey”);
INSERT INTO users (name, state,
age) VALUES (“Robert”, “New York”,
19);
In this case the first value is assigned to the first mentioned column, so “Molly” is assigned to the
name column, and “New Jersey” to the state column. Then for the other record, the column name
is given the value of “Robert”, the column state gets “New York”, the column age is assigned 19.
As noted above, the process of creating the database structure by using DDL followed by using the
DML Insert command will be similar to creating the database structure and loading data from
Project 2.
In addition, this resource may be helpful:
•
Oracle Insert Table: https://www.oracletutorial.com/oracle-basics/oracle-insert/
Step Three: WRITE SQL QUERIES TO RETRIEVE DATA FROM THE DATABASE
Using SQL, we can interact with the database by writing queries. Queries are a part of SQL. Here’s
what an example query looks like, using the SELECT statement:
SELECT * FROM customers;
Using this SELECTstatement, the query selects all the data from all the columns in the customer’s
table and returns data like so:
Figure 2: Customers table used for query examples
The asterisk wildcard character (*) refers to “all” and selects all the rows and columns. We can
replace it with specific column names instead — here only those columns will be returned by the
query.
SELECT FirstName, LastName FROM customers;
Adding a WHERE clause allows you to filter what gets returned:
SELECT * FROM customers WHERE age >= 30 ORDER BY age ASC;
This query returns all data from the products table with an age value of greater than 30. The use
of ORDER BY keyword just means the results will be ordered using the age column from the
lowest value to the highest. Of course, this brief (and limited) example is explained in greater
detail in the course content.
Assignment Requirements:
1. Step One: Create Oracle database tables using SQL Data Definition Language (DDL) for
each table listed in the metadata of Project 2.
a. You may need to use a combination of DROP TABLE, CREATE TABLE, and ALTER
TABLE SQL statements.
b. Make sure that entity and referential integrity are enforced by declaring a
primary key for each table (these may be composite keys) and declaring all
appropriate foreign keys.
c. Your CREATE TABLE and ALTER TABLE statements (if desired) must show integrity
constraints, as appropriate, for NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY,
REFERENCES, and CHECK constraints.
d. Be sure to save your SQL script file used to create these tables with a .sql
extension.
e. You should rerun and test your SQL script file until it runs without any errors (this
is why you’ll want to include DROP TABLE statements in another script).
2. Step Two: Populate each of your tables with at least five valid rows of data each and
show the SQL INSERT statements as you executed them. Populate other tables in your
database, as necessary, to satisfy referential integrity. Save your SQL script file. You
should test and rerun your SQL script file until it runs without any errors.
3. Step Three: Develop an SQL script file to perform the following FIVE queries and updates.
a. Use (Oracle’s) SQL Developer to create and test the SQL file.
b. You will find an Oracle database and Oracle’s SQL Developer in the virtual Lab
Broker.
c. You should test your SQL script file until it runs without any errors.
d. Queries to include the following FIVE queries:
1) Retrieve all of your customers’ names, account numbers, and
addresses (street and zip code only), sorted by account number.
2) Retrieve all of the videos rented in the last 30 days and sort in
chronological rental date order.
3) Produce a list of your distributors and all their information sorted in order by
company name.
4) Update a customer name to change their maiden name to a married
name. You can choose which row to update. Make sure that you use the
primary key column in your WHERE clause to affect only a specific row.
You may want to include a ROLLBACK statement to undo your data
update.
5) Delete a customer from the database. You can choose which row to
delete. Make sure that you use the primary key column in your WHERE
clause to affect only a specific row. You may want to include a ROLLBACK
statement to undo your data deletion.
What to Submit for Grading?
1. You may have completed this assignment by creating multiple smaller scripts instead of a
single large script. Submit all scripts (*.sql) and name them in such a way that it shows
the order that the scripts should be run in.
2. Name each file with your last name, followed by the ordering. For example:
Smith_Part_A.sql.
3. Include the script that un-does or drops work done in the prior script. Name it
accordingly. For example: Smith_Part_A_Drop.sql.
4. Include screenshots of each query execution including the result set in the screenshots.
Assignment-3’s grading rubric:
Attributes
Meets
Does Not Meet
CREATE TABLE and ALTER
TABLE SQL statements
70 points
All SQL statements are
syntactically correct and
execute without errors; all
integrity constraints are
properly declared.
0 points
Many SQL statements fail
due to syntax errors or SQL is
missing.
55 points
All SQL statements are
syntactically correct and
execute without errors.
0 points
Many SQL statements fail
due to syntax errors and/or
integrity constraint violations
or SQL is missing.
SELECT SQL statements
45 points
All SQL statements are
syntactically correct and
execute without errors.
0 points
Many SQL statements fail
due to syntax errors or SQL is
missing.
UPDATE and DELETE SQL
statements
25 points
Statements execute without
errors based on primary key
column in WHERE clause.
0 points
Statements fail due to syntax
or other errors.
SQL script file
30 points
Demonstrates full ability to
create and use an Oracle SQL
script filed.
0 points
Many errors setting up and
using an SQL script file or no
attempt made at all.
INSERT SQL statements
Additional resources that you might use:
•
•
•
•
•
Oracle Database Basics: https://www.oracletutorial.com/oracle-basics/
Oracle Data Definition Language (DDL) description: https://www.oracledba- online.com/sql/oracle_data_definition_language.htm
Oracle Insert Table: https://www.oracletutorial.com/oracle-basics/oracle-insert/
Oracle DROP Table: https://www.oracletutorial.com/oracle-basics/oracle-drop-table/
Introduction to Oracle CREATE TABLE:
https://www.oracletutorial.com/oracle- basics/oracle-create-table/
Justin Hoover
CMIS320
13Jun23
Project2
1- Entities and Attributes
1. Customers:
a. ID- Primary Key, unique ID of the customer
b. FullName – Name of the customer
c. Email – Customer email address
d. Phone – Customer phone number
e. Address – Customer mailing address
f. DOB – Customer date of birth
2. Movies:
a. ID- Primary Key, unique ID of the movie
b. Title – Movie title
c. ReleasedYear – The year the movie was released
d. RunningTime – Length of the movie in minutes
e. Description – Summary of the movie
f. Rating – Movie rating
g. Genre – Movie genre
3. Actors:
a. ID- Primary Key, unique ID of the actor
b. FullName – Name of the actor
c. DOB – Actor date of birth
4. MovieActors:
a. MovieID – Foreign Key, links to the Movies table
b. ActorID – Foreign Key, links to the Actors table
5. Directors:
a. ID- Primary Key, unique ID of the director
b. FullName – Name of the director
c. DOB – Director date of birth
6. MovieDirectors:
a. MovieID – Foreign Key, links to the Movies table
b. DirectorID – Foreign Key, links to the Directors table
7. Awards:
a. ID- Primary Key, unique ID of the award
b. Award- Name of the award (best actor, best picture, etc.)
8. MovieAwards:
a. MovieID – Foreign Key, links to Movies table
b. AwardID – Foreign Key, links to AcademyAwards table
9. Inventory:
a. ID – Primary Key, unique ID of the inventory item
b. MovieID – Foreign Key, links to Movies table
c. DistributorID – Foreign Key, links to Distributors table
d. Format – Format of the movie (DVD, VCR)
e. PurchasePrice – Purchase price of the video
f. PurchaseDate- Date the video was purchased
10. Rentals:
a. ID- Primary Key, unique ID of the rental
b. CustomerID – Foreign Key, links to Customers table
c. InventoryID – Foreign Key, links to Inventory table
d. DateOut – Date the video was rented out
e. DateIn – Date the video was returned
f. Discount – Discount on this transaction
g. ExtraFees – Extra charges for any reason
h. NormalCharge – Normal rental charge for the video (excluding any extra charges or
discount)
11. Distributors:
a. ID- Primary Key, unique ID of the distributor
b. FullName – Name of the movie distributor
c. Email – Distributor email address
d. Phone – Distributor phone number
2- Relationship Sentence Pairs
●
●
●
●
●
●
●
●
●
●
A customer can have one or many rentals.
A movie can have one or many actors.
An actor can act in one or many movies.
A movie can have one or many directors.
A director can have one or many movies.
A movie can win one or many awards.
An award can be given to one or many movies.
A movie can have one or many inventory items.
A distributor distributes one or many inventory items.
An inventory item can be rented one or many times.
3- ER Diagram
4- Metadata
Mom & Pop Database
Entity Name
Attributes
PK/FK?
Data Type
Description
Customers
ID
PK
INT
Primary Key, unique ID of the customer
FullName
No
VARCHAR
Name of the customer
Email
No
VARCHAR
Customer email address
Phone
No
VARCHAR
Customer phone number
Address
No
VARHCAR
Customer mailing address
DOB
No
DATE
Customer date of birth
ID
PK
INT
Primary Key, unique ID of the movie
Title
No
VARCHAR
Movie title
ReleaseYear
No
INT
The year the movie was released
RunningTime
No
INT
Length of the movie in minutes
Description
No
TEXT
Summary of the movie
Rating
No
Float
Movie rating
Genre
No
VARCHAR
Movie genre
ID
PK
INT
Primary Key, unique ID of the actor
FullName
No
VARCHAR
Name of the actor
DOB
No
DATE
Actor date of birth
Movies
Actors
MovieActors
Directors
MovieDirectors
Awards
MovieAwards
Inventory
Rentals
Distributors
MovieID
FK
INT
Foreign Key, links to the Movies table
ActorID
FK
INT
Foreign Key, links to the Actors table
ID
PK
INT
Primary Key, unique ID of the director
FullName
No
VARCHAR
Name of the director
DOB
No
DATE
Director date of birth
MovieID
FK
INT
Foreign Key, links to the Movies table
DirectorID
FK
INT
Foreign Key, links to the Directors table
ID
PK
INT
Primary Key, unique ID of the award
Award
No
VARHCAR
Name of the award (best actor, best picture, etc.)
MovieID
FK
INT
Foreign Key, links to Movies table
AwardID
FK
INT
Foreign Key, links to Awards table
ID
PK
INT
Primary Key, unique ID of the inventory item
MovieID
FK
INT
Foreign Key, links to Movies table
DistributorID
FK
INT
Foreign Key, links to Distributors table
Format
No
VARCHAR
Format of the movie (DVD, VCR)
PurchasePrice
No
FLOAT
Purchase price of the video
PurchaseDate
No
DATE
Date the video was purchased
ID
PK
INT
Primary Key, unique ID of the rental
CustomerID
FK
INT
Foreign Key, links to Customers table
InventoryID
FK
INT
Foreign Key, links to Inventory table
DateOut
No
DATE
Date the video was rented out
DateIn
No
DATE
Date the video was returned
Discount
No
Float
Discount on this transaction
ExtraFees
No
Float
Extra charges for any reason
NormalCharge
No
Float
Normal rental charge for the video (excluding any
extra charges or discount)
ID
PK
INT
Primary Key, unique ID of the distributor
FullName
No
VARCHAR
Name of the movie distributor
Email
No
VARCHAR
Distributor email address
Phone
No
VARCHAR
Distributor phone number