Project

SEE attatchment of mom and pop database

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 SQLs 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

SEE ATTATCHED FIGURE 1

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[1]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 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, well 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, lets 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) name age state email

1 Paul 24 Michigan 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. Heres what an example query looks like, using the SELECT statement:

SELECT * FROM customers;

Using this SELECT statement, the query selects all the data from all the columns in the customers table and returns data like so:

Figure 2: Customers table used for query examples

SEE ATTATCHED FIGURE 2: Customers table used for

The asterisk wildcard character (*) refers to all and selects all the rows and columns. We can replace it with specific column names insteadhere 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 youll 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? Name format(Waziri_Part_A.sql)

1. You may have completed this assignment by creating multiple smaller scripts instead of a single large script. Submit all scripts 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.