Question

In: Computer Science

Please submit SQL statements as a plain text file (.txt). If blackboard rejects txt file you...

Please submit SQL statements as a plain text file (.txt). If blackboard rejects txt file you can submit a zipped file containing the text file. Word, PDF, or Image format are not accepted. You do not need to show screen shot. Make sure you have tested your SQL statements in Oracle 11g.

The list of tables is:

Tables:

Cust Table:

cid, -- customer id

cname, --- customer name

cphone, --- customer phone

cemail, --- customer email

Category table:

ctid, --- category id

ctname, --- category name

parent, --- parent category id since category has a hierarchy structure, power washers, electric power washers, gas power washers. You can assume that there are only two levels.

Tool:

tid, --- tool id

tname, --- tool name

ctid, --- category id, the bottom level.

quantity, --- number of this tools

Time_unit table allowed renting unit

tuid, --- time unit id

len, --- length of period, can be 1 hour, 1 day, etc.

min_len, --- minimal #of time unit, e.g., hourly rental but minimal 4 hours.

Tool_Price:

tid, --- tool id

tuid, --- time unit id

price, -- price per period

Rental:

rid, --- rental id

cid, --- customer id

tid, --- tool id

tuid, --- time unit id

num_unit, --- number of time unit of rental, e.g., if num_unit = 5 and unit is hourly, it means 5 hours.

start_time, -- rental start time

end_time, --- suppose rental end_time

return_time, --- time to return the tool

credit_card, --- credit card number

total, --- total charge

Problem . Insert at least three rows of data to each table above. Make sure you keep the primary key and foreign key constraints. [20 points]

Solutions

Expert Solution

--Table: Customer (Primary key is cid)
---------------
insert into Customer(cid,cname,cphone,cemail) values(100,'Ramya',9500123321,'[email protected]');
insert into Customer(cid,cname,cphone,cemail) values(101,'Aaruu',9500123322,'[email protected]');
insert into Customer(cid,cname,cphone,cemail) values(102,'Saaai',9500123323,'[email protected]');


--Table: Category(Primary key is ctid)
---------------
insert into Category(ctid,ctname,parent) values(1000,'Electronics',2000);
insert into Category(ctid,ctname,parent) values(2000,'Automobile',3000);
insert into Category(ctid,ctname,parent) values(3000,'Furniture',null);


--Table: Tool(Primary key is tid and foreign key is ctid)
---------------
insert into Tool(tid,tname,ctid,quantity,time_unit) values(111,'AC',1000);
insert into Tool(tid,tname,ctid,quantity,time_unit) values(222,'Speaker',1000);
insert into Tool(tid,tname,ctid,quantity,time_unit) values(333,'Refridgerator',1000);

--Table: Time_unit (Primary key is tuid)
---------------
insert into Time_unit(tuid,len,min_len) values(10,15,2)
insert into Time_unit(tuid,len,min_len) values(20,25,3)
insert into Time_unit(tuid,len,min_len) values(30,35,4)

--Table: Tool_Price(It is a bridge table between Tool and Time_unit tables, Foreign keys are tid and tuid)
---------------
insert into Tool_Price(tid,tuid,price) values(111,10,300);
insert into Tool_Price(tid,tuid,price) values(222,20,400);
insert into Tool_Price(tid,tuid,price) values(333,30,500);

--Table: Rental(Primary key is ric and foreign keys are cid, tid and tuid)
---------------
insert into Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)
values(1,100,111,10,340,2019-10-09 12:00:00,2019-10-01 10:00:00,2019-10-12 10:00:00,435628675912,1800)
insert into Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)
values(2,101,222,20,670,2019-10-09 12:00:00,2019-10-01 10:00:00,2019-10-12 10:00:00,435628675912,2750)
insert into Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)
values(3,102,333,30,890,2019-10-09 12:00:00,2019-10-01 10:00:00,2019-10-12 10:00:00,435628675912,6950)


Related Solutions

Please submit SQL statements as a plain text file (.txt). If blackboard rejects txt file you...
Please submit SQL statements as a plain text file (.txt). If blackboard rejects txt file you can submit a zipped file containing the text file. Word, PDF, or Image format are not accepted. You do not need to show screen shot. Make sure you have tested your SQL statements in Oracle 11g. Problem 1. Please create the following tables for a tool rental database with appropriate primary keys & foreign keys. [30 points] Assumptions: Each tool belongs to a category....
The file CO2.txt, found on Blackboard with this assignment, contains 50 numbers, which represent the concentration...
The file CO2.txt, found on Blackboard with this assignment, contains 50 numbers, which represent the concentration of atmospheric carbon dioxide (parts per million) recorded at Mauna Loa, HI. The data in the file are the CO2 values on May 15th of each year from 1961 through 2010, (with background level CO2 removed). Fit an exponential model. Use the model to predict the CO2 value for May 15, 2015. Print the result to the screen using fprintf. The actual value was...
Create one sql script file to complete the following. You cannot run separate SQL statements for...
Create one sql script file to complete the following. You cannot run separate SQL statements for the homework. You will also need to place a semicolon after each SQL statement, a requirement for SQL files containing multiple SQL statements Lesson 3 Write a query to display the current date. Label the column DATE. Display the last name of all employees who have an A and an E in their last name. For each employee, display the employee number, last_name, salary,...
Exercise 2: You are provided with a text file named covid19-3.txt. It reports a few confirmed...
Exercise 2: You are provided with a text file named covid19-3.txt. It reports a few confirmed cases of covid19. It consists of three columns. The 1st column indicates the names of the provinces, the 2nd column indicates the names of the countries and the 3rd column indicates the numbers of confirmed cases. To do: 1. Define a function that reads, from covid19-3.txt, provinces, countries and confirmed cases in three separate lists. 2. Define a function that iterates through a list...
For Assignment 2, submit a word or pdf file with the SQL queries along with screenshots...
For Assignment 2, submit a word or pdf file with the SQL queries along with screenshots of the outputs. (It is ok if the whole problem cannot be answered, if possible, I just would like an idea of how to begin, thanks in advance!) 9. Write a query to count the number of invoices. 10. Write a query to count the number of customers with a balance of more than $500. 11. Generate a listing of all purchases made by...
Python class Select a free ebook on gutenberg . org and download the plain text file...
Python class Select a free ebook on gutenberg . org and download the plain text file (utf8). the ebook is (The Devil's Dictionary by Ambrose Bierce) Write a program that does the following: Read in your ebook (any book, any language) Break the book into words (look at .split() for strings) Make a dictionary of all words and how often they occur: for instance if the word'the'happened 2000 time and the word'a' happened 1940 times word_dict= {'the' : 2000, 'a'...
A text file called coit20245.txt consists of N lines each containing a name and a family...
A text file called coit20245.txt consists of N lines each containing a name and a family name as follows. N is largest digit of your student id number. Example of coit20245.txt file: Daniel Atkinson Rob Jackson Brian Lara Write a main() method that reads the rows of coit20245.txt and counts the lines which starts with a first letter of your name. You are to provide class comments that describe what the application does. You are also to provide comments that...
Use Python to Complete the following on a single text file and submit your code and...
Use Python to Complete the following on a single text file and submit your code and your output as separate documents. For each problem create the necessary list objects and write code to perform the following examples: Sum all the items in a list. Multiply all the items in a list. Get the largest number from a list. Get the smallest number from a list. Remove duplicates from a list. Check a list is empty or not. Clone or copy...
Python class Select a free ebook and download the plain text file (utf8). Write a program...
Python class Select a free ebook and download the plain text file (utf8). Write a program that does the following: Read in your ebook (any book, any language) Break the book into words (look at .split() for strings) Make a dictionary of all words and how often they occur: for instance if the word'the' happened 2000 time and the word'a' happened 1940 times word_dict= {'the' : 2000, 'a' :1940} Print the dictionary of word frequencies to a file (freqs.txt) with...
using JUPYTER notebook: 9.1 (Class Average: Writing Grades to a Plain Text File) Figure 3.2 presented...
using JUPYTER notebook: 9.1 (Class Average: Writing Grades to a Plain Text File) Figure 3.2 presented a classaverage script in which you could enter any number of grades followed by a sentinel value, then calculate the class average. Another approach would be to read the grades from a file. In an IPython session, write code that enables you to store any number of grades into a grades.txt plain text file. In an IPython session, write code that reads the grades...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT