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)
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 just pick songs you like). The more, the better. Once filling in the main tables CUSTOMER, ORDER_RECORD, 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.
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_RECORD | 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
|
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started