23F-A2 SO (1)

COMP 3005 Assignment #2 Due: Oct. 10 @11:59PM Instruction 1. Do the assignments independently. Copying is not allowed. 2. The database for this assignment is the same as in Assignment #1. Do this assignment directly on this document and rename it with your last + first name and submit to brightspace . Scanned handwritten documents won’t be accepted. Make sure your uploaded file can be opened. 3. You need to download and install Oracle VM version 3 on your personal computer running intel chips in order to run TRC and DRC. Note that they only work partially. Part 1 Concepts (20 marks) Explain the following concepts based on the definitions given in the lecture notes. Different answers found online will be marked wrong. The explanation should be complete; i.e, it does not contain any concept not explained here. Each concept is 2 marks. 1. Atomic Value: a value that is indivisible 2. Tuple: an ordered set of values 3. Mini World: part of the real world the database is built for 4. Database: a collection of related data stored for users to easily find what they are looking for 5. Database System: database and the application program for easy access to the database. 6. DBA: is the person to install DBMS, control its use, monitor its efficiency of operations, authorize access to the database.] 7. End User: the person uses the database in day to day basis but don’t know how the database is organized 8. Data Model: it specifies how data are organized and operated 9. Relational Data Model: data are organized as sets of tuples called relations that can be accessed using a query language. 10. Database Schema: it specifies the structure and integrity constraints of the databases. Part 2 (80 marks) Given the employees and projects databases the same as in Assignment #1. Use both Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC) to express the same queries as in Assignment Submit your query expressions for each query as well as the query result. Each query is 8 marks, 4 for TRC and 4 for DRC. (80) Marking instruction: 1. if a student used SQL instead, just give 0 for this part. Otherwise, deduct the marks accordingly as in A1. 2. Screenshots are not required. 1) Get the age of Last . TRC: {E.Age | E in Employees and E.name='Last')}; DRC: {A | (exists E) Employees(E, 'Last', A ,_ )}; or {A | Employees(_, 'Last', A ,_ )}; 2) Get the name of Last’s manager TRC: {E1.name | E1 in Employees and (exists E in Employees)( E.name='Last' and E.manager=E1.E#)}; DRC: {N| (exists M)(Employees(_, 'Last',_ ,M) and Employees(M, N,_, _))};

3) Get the name of the employee who works on GPU project. TRC: {E.name | E in Employees and (exists W in Workon, P in Projects)(P.name = 'GPU' and E.E# = W.E# and W.P# = P.P#)}; DRC: {N | (exists E, P)(Employees(E,N,_,_) and Workon(E,P,_) and (Projects(P, 'GPU',_)}; 4) Get the name of the employee who does not work on any project. TRC: {E.name | E in Employees and not (exists W in Workon)(E.E# = W.E#)}; DRC: {N | (exists E)(Employees(E,N,_,_) and not Workon(E,_,_))}; 5) Get the pair of employee name and project name such that the employee works on the project less than 300 hours. TRC: {E.name, P.name | E in Employees and P in Projects and (exists W in Workon) (E.E# = W.E# and W.P# = P.P# and W.Hours < 300)}; DRC: {EN,PN |(exists E, P, H)(Employees(E,EN,_,_) and Workon(E,P,H) and Projects(P, PN,_) and H<300)}; 6) Get the name of the employee who works on every project TRC: {E.name | E in Employee and (forall P in Projects) ((exists W in Workson)(W.E# = E.E# and W.P# = P.P#))}; DRC: {N | (exists E)(Employees(E,N,_,_) and (forall P)(not Projects(P,_,_) or Workon(E,P,_)))}; 7) Get the name of the employee who works on every project except SSD. TRC: {E.name | E in Employees and (forall P in Projects) (P.Name = 'SSD' and not (exists W in Workon)(E.E# = W.E# and W.P# = P.P#)) or (P.name <> 'SSD' (exists W in Workon)(E.E# = W.E# and W.P# = P.P#))}; DRC: {N | (exists E)(Employee(E,N,_,_) and (forall P) (not (exists M)(Projects(P,M,_) and (M <> 'SSD' or Workson(E,P,_))) or (not (exists M)(Projects(P,M,_) and M = 'SSD' or not Workson(E,P,_))))}; 8) Get the name of the employee who works on every project that Clark works on. TRC: {E1.name | E1 in Employees and E1.name != 'Clark' and (exists E in Employee)(E.name = 'Clark' and (forall P in Projects)(exists W in Workon, W1 in Workon) (E.E# = W.E# and W.E# = P.E# and E1.E# = W1.E# and W1.P# = P.P#)))}: DRC: {N | (exists E1,E)(Employee(E1,N,_,_) and N <> ‘Clark’ and (Employee(E,’Clark’,_,_) and (forall P)(not Workson(E,P,_)) or Workson(E1,P,_)))}; 9) Get the name of the employee who works on the same projects that Clark works on. TRC: {E1.name | E1 in Employees and E1.name != 'Clark' and (exists E in Employees)(E.name = 'Clark' and