1. Write a script that implements the following design: users downloads product

Question

1. Write a script that implements the following design: users downloads product product id product name user id email address first name last name download id user id download date filename product id Data type of user id, download id and product id must be NUMBER. Data type of first name and last name must be VARCHAR2(10), use data types you think suitable for other attributes, add common column attributes (NOT NULL, UNIQUE, DEFAULT) you think necessary In the Downloads table, the user id and product id columns are the foreign keys Create the sequences for the user id, download id, and product id columns Include any indexes that you think are necessary Write a script that adds rows to the database that you created in exercise 1. Add two rows to the Users and Product tables. 2. Add three rows to the Downloads table: one row for user 1 and product 2; one row for user 2 and product 1; and one row for user 2 and product 2. Use the SYSDATE function to insert the current date into the download date column Use the sequences created in the previous exercise to get the values for the user id, download id, and product id columns. Other values you enter are from the following table (DOWNLOAD DATE will be current date): FIRST NAME LAST NAME DOWNLOAD DATE FILENAME PRODUCT NAME EMAIL_ADDRESS johnsmithegmail.com John janedoe@yahoo.com Jane janedoe@yahoo.com Jane Smith Doe Doe 17-JUN-14 17-JUN-14 17-JUN-14 pedals_are falling.mp3 Local Music Vol 1 turn_signal.mp3 one horse town.mp3 Local Music Vol 1 Local Music Vol 2

Explanation / Answer

Creating User Table

Create Table users(user_id int NOT NULL AUTO_INCREMENT,email_address varchar(15),first_name varchar2(20),last_name varchar2(20))

Creating product table

Create Table product(product_id int NOT NULL AUTO_INCREMENT,produt_name varchar(50))

Creating Table downloads

Create Table downloads(download_id NOT NULL AUTO_INCREMENT,user_id int References users(user_id),download_date date,filename varchar(15),product_id References product(product_id))

Question 2

Inserting Into user table

Insert into users values ('JhonSmith@xxx.com','Jhon','Smith')

Insert into users values('Janedoe@xxx.com','Jane','Doe')

Inserting Into Product Table

Insert Into product values('Local Music Vol 1')

Insert Into product values('Local Music Vol 2')

Inserting Into downloads Table

Insert Into downloads Values(1,sysdate(),'pedals_are.mp3',1)

Insert Into downloads Values(2,sysdate(),'turn_signal.mp3',1)

Insert Into downloads Values(2,sysdate(),'one_horse.mp3',2)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.