23F-A1 SO (1)

pdf

School

Carleton University *

*We aren’t endorsed by this school

Course

3005

Subject

Computer Science

Date

Dec 6, 2023

Type

pdf

Pages

Uploaded by CountMeerkat3468

COMP 3005 Assignment #1 Due: Sept. 27@11:59PM Instruction 1. Do the assignments independently. Copying is not allowed. 2. First replace Last in the Employee table with your own last name. If your last name is not shown correctly in the result, you will get 0 for the assignment. 3. 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. 4. You need to use either Openstack or install Oracle VM on your personal computing running intel chips. Macbooks with M1 chips cannot use Oracle VM. Detail about how to install and use Oracle VM can be found in the file Oracle-VM2023.docx. 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. Data: a value that represents known facts with an implicit meaning 2. Domain: a set of atomic values with a name and/or data-type/format 3. Attribute: the column name that gives the meaning of the data in the column 4. Key: minimum set of attributes that uniquely identifies tuples in the relation 5. Primary Key: a chosen key 6. Key integrity constraints: key attribute values are unique. 7. Entity Integrity Constraint: the primary key values are unique and not null 8. Referential Integrity Constraint: tuples in the referencing relation have attributes with foreign key attributes reference the primary key attributes of the referenced relation 9. Data Definition Language (DDL): language used to specify relation schemas of a database 10. Query Language (QL): language used to specify database retrievals Part 2 (80 marks) Given the following employee and project databases with three relations. Use Oracle DBMS to create, populate and query this database. Test your statements on Oracle DBMS and take necessary screenshots to show your statements executed successfully. Copy and paste the corresponding SQL statements into this assignment. 1. Use SQL Data Definition Language to create the database. You should properly define primary keys and foreign keys with other 8 unique integrity constraints of different kinds. You can use check more than once. Boldface every integrity constraints. (10) create table Employees( E# char(5) Primary Key, Name varchar2(10) unique not null , Age int, Manager char(5), CHECK(age > 0 and Age < 60),

foreign key (Manager) references Employee(E#)); create table Projects( P# char(5) Primary Key, Name varchar2(10), Location varchar2(5) , CHECK(Location in ('B1', 'B2', 'B3')), CHECK(Name in ('CPU', 'GPU', 'SSD'))); create table Workon( E# char(5), P# char(5), Hours int, check (hours > 0 and hours < 1000), primary key(E#,P#), foreign key(E#) references Employee(E#), foreign key(P#) references Project(P#)); 2. Use SQL Data Manipulation Language to populate the database with the data given in the tables. (10) insert into Employees values('E1', 'Adams', 50, null); insert into Employees values('E2', 'Blake', 40, 'E1'); insert into Employees values('E3', 'Clark', 34, 'E2'); insert into Employees values('E4', 'David', 30, 'E3'); insert into Employees values('E5', 'Emily', 25, 'E4'); insert into Employees values('E6', 'Last', 20, 'E5'); insert into Projects values('P1', 'CPU', 'B1'); insert into Projects values('P2', 'GPU', 'B2'); insert into Projects values('P3', 'GPU', 'B2'); insert into Projects values('P4', 'SSD', 'B3'); insert into Workon values('E1', 'P1', 700); insert into Workon values('E2', 'P1', 300); insert into Workon values('E2', 'P2', 200); insert into Workon values('E3', 'P1', 100); insert into Workon values('E3', 'P2', 200); insert into Workon values('E3', 'P3', 300); insert into Workon values('E4', 'P1', 100); insert into Workon values('E4', 'P2', 200); insert into Workon values('E4', 'P3', 300); insert into Workon values('E6', 'P1', 200); insert into Workon values('E6', 'P2', 300); insert into Workon values('E6', 'P3', 400); insert into Workon values('E6', 'P4', 500); 3. Use Relational Algebra (ALG) to express the following queries based on the above database. Submit your ALG expressions for each query as well as the query result. Each query is 4 marks and the result is 2 marks (60) Employees E# Name Age Manager

1) Get the age of Last . 2) Get the name of Last’s manager 3) Get the name of the employee who works on GPU project. E1 Adams 50 E2 Blake 40 E1 E3 Clark 35 E1 E4 David 30 E3 E5 Emily 25 E4 E6 Last 20 E5 Workon E# P# Hours E1 P1 700 E2 P1 300 E2 P2 200 E3 P1 100 E3 P2 200 E3 P3 300 E4 P1 100 E4 P2 200 E4 P3 300 E6 P1 200 E6 P2 300 E6 P3 400 E6 P4 500 Projects P# Name Location P1 CPU B1 P2 GPU B2 P3 GPU B2 P4 SSD B3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

4) Get the name of the employee who does not work on any project. 5) Get the pair of employee name and project name such that the employee works on the project less than 300 hours.

6) Get the name of the employee who works on every project 7) Get the name of the employee who works on every project except SSD.

8) Get the name of the employee who works on every project that Clark works on. 9) Get the name of the employee who works on the same projects that Clark works on.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

10) Get the name of the employee who works on more than two projects.

23F-A1 SO (1)

Related Documents