Logical data models, or relational models in our case, consist of tables and the
ID: 3702486 • Letter: L
Question
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 won’t 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
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
Explanation / Answer
If you have any doubts, please give me comment...
CREATE TABLE CUSTOMER(
CustomerID INTEGER(6) NOT NULL PRIMARY KEY,
CustomerName VARCHAR(20),
City VARCHAR(20),
State CHAR(2),
PostalCOde CHAR(2),
PhoneNumber CHAR(10),
BirthDay DATE,
RegistrationDate DATE
);
CREATE TABLE OrderID(
OrderID INTEGER(10) NOT NULL PRIMARY KEY,
OrderDate DATE,
PaymentMethod CHAR(2),
OrderType CHAR(2),
CustomerID INTRGER(6),
FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID)
);
CREATE TABLE DIGITAL_ORDER(
`D-OrderID` INTEGER(10) NOT NULL PRIMARY KEY,
SubscriptionOption CHAR(1)
);
CREATE TABLE PHYSICAL_ORDER(
`P-OrderID` INTEGER(10) NOT NULL PRIMARY KEY,
DeliveryOption CHAR(1)
);
CREATE TABLE SONG(
SongID INTEGER(10) NOT NULL PRIMARY KEY,
SongTitle VARCHAR(50),
PlayTime VARCHAR(3)
);
CREATE TABLE PLAY_HISTORY(
`D-OrderID` INTEGER(10),
SongID INTEGER(6),
PlayDate DATE,
PlayCount INTEGER(3),
PRIMARY KEY(`D-OrderID`, SongID),
FOREIGN KEY(`D-OrderID`) REFERENCES DIGITAL_ORDER(`D-OrderID`),
FOREIGN KEY(`SongID`) REFERENCES SONG(`SongID`)
);
CREATE TABLE ALBUM(
AlbumID INTEGER(10) NOT NULL PRIMARY KEY,
AlbumTitle VARCHAR(50),
AlbumPrice INTEGER(3),
ReleaseDate DATE
);
CREATE TABLE ORDER_LINE(
`P-OrderID` INTEGER(10),
AlbumID INTEGER(10),
QuantitiesOrdered INTEGER(3),
PRIMARY KEY(`P-OrderID`, AlbumID),
FOREIGN KEY(`P-OrderID`) REFERENCES PHYSICAL_ORDER(`P-OrderID`),
FOREIGN KEY(`AlbumID`) REFERENCES ALBUM(AlbumID)
);
CREATE TABLE ARTIST(
ArtistID INTEGER(10) NOT NULL PRIMARY KEY,
ArtistName VARCHAR(20),
DebutData DATE
);
CREATE TABLE GENRE(
GenreID INTEGER(4) NOT NULL PRIMARY KEY,
GenreName VARCHAR(50)
);
CREATE TABLE CONTAINS(
SongID INTEGER(10),
AlbumID INTEGER(10),
PRIMARY KEY(SongID, AlbumID),
FOREIGN KEY(SongID) REFERENCES SONG(SongID),
FOREIGN KEY(AlbumID) REFERENCES ALBUM(AlbumID)
);
CREATE TABLE WRITES(
SongID INTEGER(10),
ArtistID INTEGER(10),
PRIMARY KEY(SongID, ArtistID),
FOREIGN KEY(SongID) REFERENCES SONG(SongID),
FOREIGN KEY(ArtistID) REFERENCES ARTIST(ArtistID)
);
CREATE TABLE WRITES(
SongID INTEGER(10),
GenreID INTEGER(4),
PRIMARY KEY(SongID, GenreID),
FOREIGN KEY(SongID) REFERENCES SONG(SongID),
FOREIGN KEY(GenreID) REFERENCES GENRE(GenreID)
);
INSERT INTO CUSTOMER VALUES(101, 'ANUNAGA', 'Guntur', 'AP', '522414', '9876543210', '10-Mar-1993'),(102, 'ANUNAGA2', 'Guntur2', 'AP', '522414', '9856543210', '10-Mar-1993');
You have to write this way as per your informaton... We are Indians, may be our data doesn't match with your data... Other wise give me data as comment...
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.