Logical data models, or relational models in our case, consist of tables and their PKs, FKs, and attributes to represent conceptual data models (e.g., ERD) in the structure of the relational database. In Assignment 3, you are going to use SQL to create and populate a database based on the relational model from Assignment 2. The table definition derived from the relational model is provided below to provide necessary information about the tables and attributes in detail.

This assignment is essentially two-part: 1) creating tables on your Oracle database and 2) inserting records into those tables. The first part is to use DDL and create all tables with relevant attributes as specified in the table definition. The name, data type, and length of the columns you created must follow the definitions. It is recommended to create constraints to enforce the data integrity. In the second part, you need to populate the tables using DML. Your database must have at least 3 albums, 20 songs, 5 customers, and 10 orders (You could make up fake songs, but it would be much easier to fill out the columns if you pick actual songs). The more, the better. Once filling in the main tables CUSTOMER, ORDER, SONG (ARTIST and GENRE come along with SONG), and ALBUM, you can quickly populate the remaining matching (or associative) tables WRITES, BELONGS_TO, CONTAINS, PLAY_HISTORY, and ORDER_LINE. Some example codes are included for understanding at the end of the document.

You need to turn in a script that contains all SQL lines to execute the above requirements; it would look similar to the JustLeeDB file used in the class. To obtain a perfect score in this assignment, your script must produce the tables and records that meet the requirements and be executable without generating any errors. If your code fails to run on my computer, I will look into it to give you partial credits, but you wont get full marks. Run your code on your computer before submission to make sure everything is alright.

Deliverables

A script file that contains all SQL lines to execute the above requirements

Save it as a text file (txt)

Run your script on your computer before submission to make sure everything is alright.

The file name should be 7510_asgmt3_lastname_firstname.txt (e.g., 7510_asgmt2_ lee_kyunghee.txt)

File extension should be txt.

Table Definition

Table	Column	Data Description	Length	Description/Possible Values
CUSTOMER	CustomerID	Numeric	6	Primary key
	CustomerName	Character	20
	City	Character	20
	State	Fixed character	2
	PostalCode	Fixed character	5
	PhoneNumber	Fixed character	10
	Birthday	Date
	RegistrationDate	Date
ORDER	OrderID	Numeric	10	Primary key
	OrderDate	Date
	PaymentMethod	Fixed character	2	CS(Cash), CC(Credit Card), or PP(Paypal)
	OrderType	Fixed character	1	P(Physical order), D(Digital order)
	CustomerID	Numeric	6	Foreign key
DIGITAL_ORDER	D-OrderID	Numeric	10	Primary key; Foreign key
	SubscriptionOption	Fixed character	1	M(Monthly) or Y(Yearly)
PHYSICAL_ORDER	P-OrderID	Numeric	10	Primary key; Foreign key
	DeliveryOption	Fixed character	1	F(Free), S(Standard), or E(Expedite)
PLAY_HISTORY	D-OrderID	Numeric	10	Primary key; Foreign key
	SongID	Numeric	10	Primary key; Foreign key
	PlayDate	Date
	PlayCount	Numeric	3	Number of times of a song played per day (e.g., 10)
ORDER_LINE	P-OrderID	Numeric	10	Primary key; Foreign key
	AlbumID	Numeric	10	Primary key; Foreign key
	QuantitiesOrdered	Numeric	3
SONG	SongID	Numeric	10	Primary key
	SongTitle	Character	50
	PlayTime	Numeric	3	Playtime of a song in minute (e.g., 3)
ALBUM	AlbumID	Numeric	10	Primary key
	AlbumTitle	Character	50
	AlbumPrice	Numeric	3
	ReleaseDate	Date
ARTIST	ArtistID	Numeric	10	Primary key
	ArtistName	Character	20
	DebutDate	Date
GENRE	GenreID	Numeric	4	Primary key
	GenreName	Character	50
CONTAINS	SongID	Numeric	10	Primary key; Foreign key
	AlbumID	Numeric	10	Primary key; Foreign key
WRITES	ArtistID	Numeric	10	Primary key; Foreign key
	SongID	Numeric	10	Primary key; Foreign key
BELONGSTO	SongID	Numeric	10	Primary key; Foreign key
	GenreID	Numeric	4	Primary key; Foreign key