1.1.Entity relationship diagram
![]()
2.List of relations
Entity
|
Atribute
|
Type
|
Constraint
|
Student
|
studentID
|
NUMBER
|
Primary key
|
|
Name
|
VARCHAR(50)
|
|
|
Phone
|
VARCHAR(25)
|
|
|
address
|
VARCHAR(50)
|
|
course
|
courseCode
|
NUMBER
|
Primary key
|
|
title
|
VARCHAR(50)
|
|
Course_prerequisites
|
Course_code
|
NUMBER
|
Primary key
Foreign key references course.courseCode
|
|
prerequisiteID
|
NUMBER
|
Primary key
Foreign key references course.courseCode
|
enrollment
|
enrollmentID
|
NUMBER
|
Primary key
|
|
Type
|
VARCHAR(50)
|
|
|
Date
|
DATE
|
|
|
studentID
|
NUMBER
|
Foreign key references student.studentID
|
|
coursecode
|
NUMBER
|
Foreign key references course.courseCode
|
Scholarship_enrollment
|
enrollmentID
|
NUMBER
|
Primary key
Foreign key references enrollment.enrollmentID
|
|
granter
|
VARCHAR(50)
|
|
|
Year
|
NUMBER(4)
|
|
|
Amount
|
NUMBER
|
|
instructor
|
instructorID
|
NUMBER
|
Primary key
|
|
Name
|
VARCHAR(50)
|
|
|
Phone
|
VARCHAR(25)
|
|
|
address
|
VARCHAR(50)
|
|
Skills_certifications
|
skillID
|
NUMBER
|
Primary key
|
|
Name
|
VARCHAR(50)
|
|
|
instructorID
|
NUMBER
|
Foreign key references instructor.instructorID
|
Course_offering
|
OfferingID
|
NUMBER
|
Primary key
|
|
courseCode
|
NUMBER
|
Foreign key references course.courseCode
|
|
instructorID
|
NUMBER
|
Foreign key references instructor.instructorID
|
|
Classtime
|
VARCHAR(10)
|
|
|
Classroom
|
VARCHAR(10)
|
|
|
enrollmentCapacity
|
NUMBER
|
|
item
|
itemID
|
NUMBER
|
Primary key
|
|
Name
|
VARCHAR(50)
|
|
|
Price
|
NUMBER
|
|
|
Category
|
VARCHAR(50)
|
|
supplier
|
supplierID
|
NUMBER
|
Primary key
|
|
companyName
|
VARCHAR(50)
|
|
|
Phone
|
VARCHAR(25)
|
|
|
Address
|
VARCHAR(50)
|
|
supplierItems
|
supplierID
|
NUMBER
|
Primary key
Foreign key references supplier.supplierID
|
|
itemID
|
NUMBER
|
Primary key
Foreign key references item.itemID
|
Course_items
|
Coursecode
|
NUMBER
|
Primary key
Foreign key references course.courseCode
|
|
itemID
|
NUMBER
|
Primary key
Foreign key references item.itemID
|
3.oracle sql create table
Create table enrollment(
enrollmentID number not null primary key,
type varchar2(25) not null,
enrollmentDate date not null,
courseCode number not null,
studentID number not null,
constraint enrollment_fk1 foreign key (coursecode) references course (coursecode),
constraint enrollment_fk2 foreign key (studentID) references student (studentID)
2.
CAR PURCHASE CONTRACT (contract number, contract date, details of purchase, total purchase amount, car delivery date, customer number, customer name, customer address, customer email, car stock number, car registration number, make, model, colour, body type, VIN number, engine number, manufacture date, manufacturer code, manufacturer name, manufacturer contact details)
Normalization follows the following steps;
- Normalization to 1NF- involves elimination of all repeating groups;
- Normalization o 2NF- involves normalizing to eliminate all the partial dependencies
- Normalization to 3NF – involves normalizing to eliminate all the transitive dependencies.
1NF
No repeating groups thus the relation is already in 1NF.
2NF
No partial dependencies identified thus the relation is in 2NF.
3NF
By eliminating the transitive dependencies the following relations are achieved;
Car_purchase_contract (contractNumber, contractDate, details o fPurchase, totalAmount,deliveryDate,customerNumber, carStockNumber)
Customer (customerNumber, customerName, customreAddress, customerEmail)
Car (carStockNumber, car Registration Number, make, model, colour, bodyTpe, VINNumber, engineNumber,manufacturerDate, manufacturerCode)
Manufacturer (manufacturerCode, mnufacturerName, manufacturerContacts)
3.
1.Display the category, book title, retail cost (formatted as $XXX.XX) and gift details for all books which are eligible for a gift. Order the result by category and retail cost (retail cost in descending order)
select category, title,to_char(retail,'$9,999.99') from books,promotion where books.retail between promotion.minRetail and promotion.maxretail order by books.category, books.retail desc;
2.Display the last name, first name, order number for all customers who have placed an order for which they have not received a free gift of “BOOK LABELS” listed in the promotion table. Here we want to use the amount that they paid for each item to see is they were eligible for a gift of “BOOK LABELS”. The gifts are only available for the price paid and does not take into consideration the quantity of a book purchase.
select customers.customer#,customers.lastname, customers.firstname, orders.order# from customers,orders,orderitems,books where customers.customer#=orders.customer# and orders.order#=orderitems.order# and orderitems.isbn=books.isbn and books.retail between (select minretail from promotion where gift='BOOK LABELS') and (select maxretail from promotion where gift='BOOK LABELS');
3.Display the unique customer number, customer last name, customer first name for all customers who have bought a book that was co-authored by more than 2 authors. Order the resulting set by customer number.
select customers.customer#,customers.lastname, customers.firstname, orders.order# from customers,orders,orderitems,books where customers.customer#=orders.customer# and orders.order#=orderitems.order# and orderitems.isbn=books.isbn and books.isbn in (select isbn from bookauthor having count(distinct(isbn))>2 group by isbn);
4.Display the unique customer number, customer last name, customer first name for all customers who have bought a book that was co-authored by more than 2 authors. Order the resulting set by customer number.
select books.title,publisher.name from books,publisher where books.pubID=publisher.pubID and books.isbn in (select isbn from bookauthor group by isbn having count(distinct(isbn))=1) and books.isbn not in (select isbn from orderitems);
![]()
Display the publisher name, contact details and phone number for all the publisher that have had sales of more than 5 book from the list of books they publish
select publisher.name,publisher.contact,publisher.phone from publisher inner join books on books.pubID=publisher.pubID inner join orderitems on orderitems.isbn=books.isbn group by publisher.name,publisher.contact,publisher.phone having count(distinct(orderitems.isbn))>=5;
5.Display the state, count of number of orders (rename the field “Number of Orders”), and the number of book categories (rename the field “Number of Categories”) for all states that have the number of book categories same as the highest number of book categories in all the states. We are looking for states that buy the most diverse categories of books.
select shipstate,count(orders.order#) as "Number of orders",count (books.category) as "Number of categories" from orders inner join orderitems on orders.order#=orderitems.order# inner join books on books.isbn=orderitems.isbn group by shipstate having count(books.category)=(select max(count(books.category)) from orders inner join orderitems on orders.order#=orderitems.order# inner join books on books.isbn=orderitems.isbn group by orders.shipstate);