Description

Objectives:
The purpose of this lab is to introduce you to the DDL set of statements in SQL. By writing SQL to create tables, constraints, and views, you will have the tools needed to implement database designs that you will create later in the course. By finishing this lab, the student will be able to:
• create, modify, and drop tables based on design specifications provided
• enforce constraints on tables to ensure data integrity and consistency
• create a table using the structure and data from an existing table
• Import data into a table from other tables

Submission:
Your submission will be a single text-based .sql file with the solutions provided. No other file format would be accepted.
DBS211_L05_FirstName_LastName.sql
Your submission needs to include a comment header block and be commented to include the questions and the solutions. Make sure every SQL statement terminates with a semicolon.
Example Submission
— ***********************
— Name: Your Name
— ID: #########
— Purpose: Lab 05 DBS211
— ***********************

— Q1 SOLUTION —
SELECT * FROM TABLE;

— Q2 SOLUTION – SELECT * FROM TABLE;

Locate, select, and submit the file to the Lab 03 link.

Setup

Style Guide
Your SQL should be written using the standard coding style:
• all keywords are to be in upper case
• all user-defined names are to be in lower case (example: table and field names)
• there should be a carriage return before each major part of the SQL statement
See the following sample:

CREATE TABLE table_name( column1 DATATYPE NULL, column2 DATATYPE NOT NULL);

Marking Scheme
Question Points
1 a. 0.5
1 b. 0.5
1 c. 0.5
1 d. 0.5
2 0.5
3 0.5
4 a. 0.5
4 b. 0.5
Total: 4

Grade Policy
▪ Submissions with errors do not get any marks. (They get zero.) o Execute your .sql file using the “Run Script” button in SQL Developer to make sure there are no errors in your file.
▪ You do not receive marks for the missing or incomplete solutions.
▪ The name of the tables you will create in this lab have to match exactly with the names given in this lab document.

Tasks:

1. Create the following tables and their given constraints:

a. L5_MOVIES
Column Name Column DataType PK Not Null Unique FK Default Value Validation
m_id NUMBER ✓
title VARCHAR2(35) ✓ ✓
release_year NUMBER ✓
director_id NUMBER ✓
score NUMBER(3,2) 2.5 < 5 and > 0

b. L5_ACTORS
Column Name Column DataType PK Not Null Unique FK Default Value Validation
a_id NUMBER ✓
first_name VARCHAR2(20) ✓
last_name VARCHAR2(30) ✓

c. L5_CASTINGS
Column Name Column DataType PK Not Null Unique FK Default Value Validation
movie_id NUMBER ✓ ✓
(L5_MOVIES)
actor_id NUMBER ✓ ✓
(L5_ACTORS)

d. L5_DIRECTORS
Column Name Column DataType PK Not Null Unique FK Default Value Validation
director_id NUMBER ✓
first_name VARCHAR2(20) ✓
last_name VARCHAR2(30) ✓

2. Modify the L5_MOVIES table to create a foreign key constraint that refers to the table L5_DIRECTORS.

4. a. Write SQL statements to remove all of the above tables.
b. Is the order of tables important when removing? Why (write the answer as code comments)?