Assignment 4
pdf
keyboard_arrow_up
School
Lambton College *
*We aren’t endorsed by this school
Course
123
Subject
Information Systems
Date
Jan 9, 2024
Type
Pages
16
Uploaded by BaronPheasant3883
CBD 2303 Assignment 4
Create tables and debug errors
You should submit by a word file, and mention all the group members who work for your submission. As a fresher, the question below may take time, but it’s worth it. For each new error you see in the feedback, you can google the ”ORA-numbers”, and you can find the definition and solutions (Question No.1 takes 40%, and the rest takes 30% for each).
1.
You are now going to create the following set of tables (this question iscounted as 3 exercises):
Figure 1: Question 1
CUSTOMER (
Customer No
, Customer Name, Customer Address, Customer Tel No)
BOOKING (
Booking No
, Booking Date. Customer No
, Car Reg
)
CAR (
Car Reg
, Car Make, Car Model)
a.
Create a new script file called Bookings and create the three tablesbased on the relations above. Ensure you choose suitable column data types and sizes and do not forget to specify a primary key for each table and the two foreign keys Customer No, Car Reg in the booking table. Consider the order the tables will be created, as the table at the one end of a relationship must be created before the many end one you cannot reference a table that does not exist. Run the script file Bookings to create the tables. Edit the file to correct any errors.
(Show me the screenshot after ”run now”, I need to see the full screen with feedback page, number of statements processed, successful, and errors)
-- Creating the Customer table Drop TABLE CUSTOMER;
CREATE TABLE CUSTOMER (
Customer_No INT PRIMARY KEY,
Customer_Name VARCHAR(100),
Customer_Address VARCHAR(255),
Customer_Tel_No VARCHAR(15)
);
--Creating the Car table
Drop TABLE CAR; CREATE TABLE CAR (
Car_Reg VARCHAR(15) PRIMARY KEY,
Car_Make VARCHAR(50),
Car_Model VARCHAR(50)
);
--Creating the BOOKING table
Drop TABLE BOOKING;
CREATE TABLE BOOKING (
Booking_No INT PRIMARY KEY,
Booking_Date DATE,
Customer_No INT,
Car_Reg VARCHAR(15),
FOREIGN KEY (Customer_No) REFERENCES CUSTOMER(Customer_No),
FOREIGN KEY (Car_Reg) REFERENCES CAR(Car_Reg)
);
b.
Go to SQL Workshop and then Object Browser, and take screen shotsafter clicking each tables on the left.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
c.
Create a new script file called Booking data, and write insert commands to insert data into all three tables. Do not forget that the data at the one end of a relationship must exist before the many end, so the booking data must be inserted last. Run the Booking data script file to insert the data. Edit the file to correct any errors.
(Show me the screenshot after ”run now”, I need to see the full screen with feedback page, number of statements processed, successful, and errors)
INSERT INTO Customer VALUES (153, 'Ankush', 'Brampton', 5826419758);
INSERT INTO Customer VALUES (173, 'Simran', 'Brampton', 8974562166);
INSERT INTO Customer VALUES (193, 'Mike', 'Vaughan', 8597537982);
INSERT INTO Customer VALUES (113, 'Albert', 'MARKHAM', 789564139);
INSERT INTO car VALUES (2323, TO_DATE ('14-01-2022','dd-mm-yyyy'),'evoque');
INSERT INTO car VALUES (0001, TO_DATE ('05-05-2010','dd-mm-yyyy'),'charger');
INSERT INTO car VALUES (0002, TO_DATE ('09-11-2019','dd-mm-yyyy'),'BMW');
INSERT INTO car VALUES (1592, TO_DATE ('19-07-2022','dd-mm-yyyy'),'Camaro');
INSERT INTO booking VALUES (12, TO_DATE ('14-01-2021','dd-mm-yyyy'),153 , 2323);
INSERT INTO booking VALUES (13, TO_DATE ('05-05-2021','dd-mm-yyyy'),173 , 0001);
INSERT INTO booking VALUES (14, TO_DATE ('09-11-2021','dd-mm-yyyy'),193 , 0002);
INSERT INTO booking VALUES (15, TO_DATE ('19-07-2021','dd-mm-yyyy'),113 , 1592);
This script shows error because I already ran this once time.
1
d.
Go to SQL Workshop and then Object Browser, and take screen shotsof Data after clicking each tables on the lest and Clicking Data on the right side.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
2. Using the SQL Workshop with your music data, SQL Commands tool enter select statements for the following (show me the statements in text form and screenshots of each outputs):
a) Select the company name, cd title, track title and music category description:
SELECT c.company_name, cd.cd_title, t.track_title, cat.cat_description
FROM company c
JOIN cd ON c.company_ID = cd.cd_company_ID
JOIN track t ON cd.cd_IDno = t.track_cd
JOIN category cat ON t.track_cat_ID = cat.cat_id;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
b) Select the names of the companies which produced cds with “Dance” tracks:
SELECT DISTINCT c.company_name
FROM company c
JOIN cd ON c.company_ID = cd.cd_company_ID
JOIN track ON cd.cd_IDno = track.track_cd
JOIN category ON track.track_cat_ID = category.cat_id
WHERE category.cat_description = 'Dance';
c) Select all cd titles for cds which include “Pop” or “Dance” category tracks
SELECT DISTINCT cd.cd_title
FROM cd
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
JOIN track ON cd.cd_IDno = track.track_cd
JOIN category ON track.track_cat_ID = category.cat_id
WHERE category.cat_description IN ('Pop', 'Dance');
3. Write SELECT statements to answer the following (show me the statements in text form and screenshots of each output):
a) Count the total number of tracks.
SELECT COUNT(*) AS total_tracks FROM track;
b) Show the maximum and minimum track lengths
SELECT MAX(track_length) AS max_length, MIN(track_length) AS min_length FROM track;
c) Display the average cd price by company for any companies with an average price smaller than 10. -> SELECT c.company_name, AVG(cd.cd_price) AS average_price
FROM cd
JOIN company c ON cd.cd_company_ID = c.company_ID
GROUP BY c.company_name
HAVING AVG(cd.cd_price) < 10;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help