Database Design, Integrity Constraints, and SQL Query Formulation

This document presents a comprehensive solution to a database assignment, focusing on relational database design and SQL query formulation. The assignment begins with identifying and describing foreign keys within the provided relations: Product, Supplier, Order, and Purchase. It then explores integrity constraints, including entity and referential integrity, by analyzing insertion operations and their potential violations. The core of the assignment involves formulating SQL queries for a given database schema consisting of Staff, Skill, Project, and Booking tables. The SQL queries cover a range of functionalities, from simple SELECT statements and filtering with WHERE clauses to aggregate functions like COUNT, GROUP BY clauses, and joins. Furthermore, the solution demonstrates the use of both JOIN and subquery methods to retrieve specific data based on criteria such as skill description and charge-out rates. The provided solutions are designed to aid students in understanding and applying database concepts and SQL query techniques.

QUESTION 2
The following relations form part of a database held in a
relational DBMS:Product (productNo, productName, price,
quantity, supplierNo): productNo is a primary key.
productNo productName price quantity supplierNo
s1 A4 paper $40 50 s1
s2 B1 paper $45 40 s2
s3 Legal Paper $100 50 s3
Supplier (supplierNo, SupplierName, Address, Contact):
supplierNo is a primary key.
supplierNo SupplierName Address Contact
s1 BMCC 199 Chamber St, NYC, NY Don
Wei
s2 Hunter 695 Park Ave
New York, NY 10065
Jochen
Albret
s3 City Tech 300 Jay St, Brooklyn, NY
11201
Hong
Li
Order (orderNo, orderDate, paymentMethod): orderNo is a
primary key.
orderNo orderDate paymentMethod Customer
o1 1/1/2015 Cash Tina Smith
o2 1/2/2015 Cash Eric Nicholas
o3 3/3/2015 Credit Mary Davis
Purchase (orderNo, productNo, quantity): (orderNo,productNo)
is a primary key.
orderNo productNo quantity
o1 p1 5
o1 p2 4
o2 p1 10

Question 2 cont
Identify and describe all the foreign keys for the above relations.
(15pts)
Note:
• Foreign Key: Attribute, or set of attributes, within one
relation that matches candidate key (or primary key) of
some (possibly same) relation.
• There are three foreign keys.
You should describe the foreign key in the format of "The
foreign key attributeName in relation name references to
the attributeName in relation name."
QUESTION 3
• Continue from question 1 to answer the following questions.
Be noted that those question are independent and NOT a
sequence of operations.Entity Integrity: In a base relation, no
attribute of a primary key can be null.Referential Integrity: If
foreign key exists in a relation, either foreign key value
must match a candidate key value of some tuple in its home
relation or foreign key value must be wholly null.Of course,
primary key values can't be duplicated.
a) If one tries to insert a new row into Product relation with ('p3',
‘Red Pencil’, 30, 100, ‘s2’). Does this insertion operation cause
violation of any integrity constraint? Why? (5 pts)
b) If one tries to insert a new row into Order relation with ('o4',
'1/1/2015', 'Cash', ‘Tina Smith’). Does this insertion operation
cause any violation of any integrity rule? Why? (5 pts)
(c) If one tries to insert a new row into Purchase relation with ('o4',
'p2', 5). Does this insertion operation cause any violation of any
integrity rule? Why? (5 pts)

Given the Database Schema and sample data as below. (60 pts; 10
pts bonus)
Staff (staffNo, name, dept, skillCode)
Skill (skillCode, description, chargeOutRate)
Project (projectNo, startDate, endDate, budget,
projectManagerStaffNo)
Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn)
Where:
Staff contains staff details and staffNo is the key. The
skillCode is a foreign key.
Skill contains descriptions of skill codes (e.g. Programmer,
Analyst, Manager, etc.) and the charge out rate per hour
for that skill; the key is skillCode.
Project contains project details and projectNo is the key.
projectManagerStaffNo is the foreign key.
Booking contains details of the date and the number of hours
that a member of staff worked on a project and the key
is (staffNo,projectNo).
Staff (staffNo, name, dept, skillCode)
staffNo name dept skillCode
s1 Don Wei Health
Information
Technology
sk1
s2 Eric Smith Payroll sk2
s3 Nick William Computer
Information
Systems
sk2

Skill (skillCode, description, chargeOutRate)
skillCode description chargeOutRate
sk1 Medical Terms $60
sk2 Accounting $80
sk3 Health Information DBA $70
sk4 Programmer $65
Project (projectNo, startDate, endDate, budget,
projectManagerStaffNo)
projectNo startDate endDate budget projectManagerStaffNo
p1 1-Jan-2014 31-Aug-
2014
$50,000 s1
p2 1-Mar-2014 May-31-
2014
$80,000 s2
Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn)
staffNo projectNo dateWorkedOn timeWorkedOn
s1 p1 15-Jan-2014 100
s1 p2 1-Apr-2014 50
Formulate the following queries using SQL statement. Don't use
MySQL Workbench.
1. (Simple Query) List all details of all staffs. (Use just
SELECT...FROM)
2. (Simple Query) List all skills with a charge out rate great
than 60 per hour, in alphabetic order of description. (Use
SELECT ...FROM ... WHERE ...ORDER BY....)
3. Aggregate Function) List out the total number of projects.
(Use COUNT(*))
4. (Group) List out the total number of projects for each project
manager. (Use GROUP BY)
5. List all the staffs with the skill
description ‘Accounting’ . Use both Join and Sub-query.

a. JOIN (Join the skillCode between the Staff and Skill
relations.)
b. SUB-QUERY (Inner query returns the skillCode for the
description of 'Accounting'. Outer query will select the
staffs who have the returned skillCode)
c. (Subquery) List out all the skill(s) that has charge out
rate equals to the max charge out rate. (Note:Use Sub-
query: inner query finds the max charge out rate that will
be used for the outer query in the WHERE clause.)