HKBU Comp 7640 Database Systems and Administration

Assignment 1 (10% of overall course marks)

1. Query Language - Consider the following relational schema: (30%)

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.

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.

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.

a)  Draw an ER diagram (in UML notations) for the above schema.

b)  Give SQL statement for the following:

                (i) List all skills with a charge out rate greater than 60 per hour, in alphabetical order of description.

(ii) How many staff have the skill ‘Programmer’?

(iii) List all projects that have at least two staff booking to it.

                (vi) List all staff with a charge out rate greater than the average charge out rate.

(v) Create a view of staff details giving the staff number, staff name, skill description, and department, but excluding the skill number and charge out rate.

c)     Use an English statement to describe the meaning of each of the following SQL queries.

                (i)            SELECT * FROM Staff s, Skill k

                                WHERE s.skillCode = k.skillCode AND

description = ‘Programmer’ AND dept = ‘Special Projects’;

                (ii)           SELECT name, p.projectNo, dateWorkedOn, timeWorkedOn

                                                FROM Staff s, Project p, Booking b

                                                WHERE s.staffNo = b.staffNo AND

b.projectNo = p.projectNo AND

endDate >= DATE ‘1995-06-01’;

d)       Formulate the two queries in part c) in relational algebra.

2. Data Catalog Design (40% )

In a (simplified) database catalog, the DBMS needs to store the following information:

· Record all the tables of the database, which all have unique names and owners (assume owner is the user who creates the table).  Tables can be base relations or views.

· The users are identified by a unique user-name and each of them has a password.

· A view is defined as the result of an SQL query, whose columns can be addressed by a name or column number.

· A base relation has:

o One or more named attributes, each of which has a data type and the NOT NULL constraint.

o One or more named indices, each of which consists of one or more attribute of the base relation. There is a specification of order direction, which must either be ASC (ascending) or DESC (descending).

o A primary key, which consists of one or more attributes of the base relation.

o One or more foreign keys, which consists of one or more attributes of the base relation, referring to attributes of (other) tables.

· Keep the access rights of users to a table: select, update, or delete.

a) Draw an ER diagram to depict your design about the entities and their relations in the database catalog.  You have to show the cardinality.  State any further assumptions that you have made in your design.  Remember to show the primary key and foreign keys.

b) Transform your ER diagram to a relational database schema in the form: 
      table ( key1, key2, col1, col2, … )  foreign key fk1 references Table(col) …
Underline the primary keys and write down foreign keys similar to the above format.  State any further assumptions that you have made in your design.  (This means: you are specifying the DBMS system catalog tables.)

c) Assume the database was first empty and there were users John (password “nhoj”), Tom (password “mot”) and Mary (password “YRAM”).  John created the following base relations, views and indices, and granted access to Tom and Mary. Show the content for the DBMS system catalog tables (conforming to your scheme in part b) after the following SQL statements were executed.

create table supplier (

        code varchar2(10) not null,  name varchar2(50), 

        address varchar2(100),  phone varchar2(20),

        primary key (code)

);

create table product (

        code varchar2(10) not null, name varchar2(50),  on_hand integer,  price number(10,2),

        primary key (code)

);

create table supplying (

        supplier_code varchar2(10) not null,  product_code varchar2(10) not null,

        price number(10,2),  on_hand integer,

        primary key (supplier_code, product_code),

        foreign key (supplier_code) references supplier(code),

        foreign key (product_code) references product(code)

);

create index supplier_name on supplier (name);

create index product_name on product(name);

create view product_desc as select code, name from product;

grant select on product_desc to Mary;

grant select on suppler to Mary, John;

grant select, update on product to John;  

grant select, update on supplying to John;

3. Normalization (30% )

The table shown below displays sample rows of a film database, including film title, director, and roles played by actors/actresses in films (and how long they are on screen).

filmNo	fTitle	dirNo	director	actorNo	aName	role	timeOnScreen
F1100	Happy Days	D101	Jim Alan	A1020	Sheila Toner	Jean Simson	15.45
F1100	Happy Days	D101	Jim Alan	A1222	Peter Watt	Tom Kinder	25.38
F1100	Happy Days	D101	Jim Alan	A1020	Sheila Toner	Silvia Simpson	22.56
F1109	Snake Bite	D076	Sue Ramsay	A1567	Steven McDonald	Tim Rosey	19.56
F1109	Snake Bite	D076	Sue Ramsay	A1222	Peter Watt	Archie Bold	10.44

a) The table shown above is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.

b) Identify the functional dependencies and the primary key represented by the table shown above. State any assumptions you make about the data shown in this table (if necessary). 

c) Using the functional dependencies identified, describe and illustrate the process of normalization by converting the table to 3NF (i.e., state which dependency violates which normal form). Identify the primary and foreign keys in your 3NF relations.

d) Are your 3NF relations also in BCNF? Why or why not?

e) Draw an Entity–Relationship model for the data shown in table above.