CIS2002 Database Design And Implementation

1.An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.

2.A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.

1.1.Entity relationship diagram

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.

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);

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;

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);