ZION COLLEGE OF TECHNOLOGY AND BUSINESS
ADVANCED DATABASE GROUP ASSIGNMENT
THIRD YEAR FIRST SEMESTER REGULAR STUDENTS
GROUP II MEMBERS
NO IDNO NAME
1 ZNRCS-88540-14 Duresa Bushaka
2 ZNRCS-830589-14 Elala Ena
3 004/14 Birtukan Assefa
4 ZNRCS-17604-14 Gedeon Dawit
5 007/14 Mitike Atinafu
Submitted to: Mr. Mekonnen
Submission date: 01/04/16 E.C
2. a) based on the given information, we can identify the following entities, relationships, and attributes:
Entities:
Professor: SSN (primary key), Name, Age, Rank, Research Specialty
Project: Project Number (primary key), Sponsor Name, Starting Date, Ending Date, Budget
Graduate Student: SSN (primary key), Name, Age, Degree Program
Department: Department Number (primary key), Department Name, Main Office
Relationships:
Project-Professor (One-to-Many)
Each project is managed by one professor (Principal Investigator).
Each project is worked on by one or more professors (Co-investigators).
Project-Graduate Student (Many-to-Many)
Each project is worked on by one or more graduate students (Research Assistants).
Each graduate student can work on multiple projects.
Professor-Department (Many-to-Many)
Professors work in one or more departments.
Each department has multiple professors.
Professor and Graduate Student (One-to-Many)
A professor can supervise multiple graduate students.
A graduate student can have multiple supervisors (professors).
Graduate Student-Department (One-to-Many)
Each graduate student has one major department.
Each department can have multiple graduate students.
Graduate Student-Graduate Student (One-to-One)
Each graduate student has a student advisor (more senior graduate student).
Attributes are already listed under each entity.
b) Here is an ER diagram representing the information about the university:
Department Professor Project
Graduate Student
c) To map the ER model into a relational data model, we can create tables based on the entities and
relationships identified:
Table: Department Table: Professor Table: Graduate
Dept_Number (PK) SSN (PK) SSN (PK)
Dept_Name Name Name
Main_Office Age Age
Chairman_SSN (FK) Ranks Degree_Program
Research_Specialty Major_Dept_Number (FK)
Advisor_SSN (FK)
Table: Project Table: Project_Professor Table: Project_Graduate
Project_Number (PK) Project_Number (FK, PK) Project_Number (FK, PK)
Sponsor_Name Professor_SSN (FK, PK) Student_SSN (FK, PK)
Starting_Date Role
Ending_Date
Budget
Principal_Investigator_SSN (FK)
d) Here are the SQL queries to create the resulting tables:
CREATE TABLE Department (
Dept_Number INT PRIMARY KEY, Dept_Name VARCHAR(255), Main_Office VARCHAR(255),
Chairman_SSN INT);
CREATE TABLE Professor (
SSN INT PRIMARY KEY, Name VARCHAR(255), Age INT, Ranks VARCHAR(255), Research_Specialty
VARCHAR(255));
CREATE TABLE Graduate (
SSN INT PRIMARY KEY, Name VARCHAR(255), Age INT, Degree_Program VARCHAR(255),
Major_Dept_Number INT, Advisor_SSN INT);
CREATE TABLE Project (
Project_Number INT PRIMARY KEY, Sponsor_Name VARCHAR(255), Starting_Date DATE, Ending_Date
DATE, Budget DECIMAL(10,2), Principal_Investigator_SSN INT);
CREATE TABLE Project_Professor (
Project_Number INT, Professor_SSN INT, Role VARCHAR(255), PRIMARY KEY (Project_Number,
Professor_SSN),
FOREIGN KEY (Project_Number) REFERENCES Project(Project_Number),
FOREIGN KEY (Professor_SSN) REFERENCES Professor(SSN));
CREATE TABLE Project_Graduate (
Project_Number INT, Student_SSN INT,
PRIMARY KEY (Project_Number, Student_SSN),
FOREIGN KEY (Project_Number) REFERENCES Project(Project_Number),
FOREIGN KEY (Student_SSN) REFERENCES Graduate(SSN));
e) Here are example SQL queries to insert at least two records for each table:
INSERT INTO Department (Dept_Number, Dept_Name, Main_Office, Chairman_SSN)
VALUES (101, 'Computer Science', 'Building A', 123456789), (102, 'Mathematics', 'Building B',
987654321);
INSERT INTO Professor (SSN, Name, Age, Ranks, Research_Specialty)
VALUES (123456789, 'Yonas Semere', 40, 'Associate Professor', 'Machine Learning'),
(234567890, 'Fikiru Dawit', 35, 'Assistant Professor', 'Data Science');
INSERT INTO Graduate (SSN, Name, Age, Degree_Program, Major_Dept_Number, Advisor_SSN)
VALUES (345678901, 'Selam Alemayehu', 27, 'M.S.', 101, 123456789),
(456789012, 'Yacob Tesfaye', 32, 'Ph.D.', 102, 234567890);
INSERT INTO Project (Project_Number, Sponsor_Name, Starting_Date, Ending_Date, Budget,
Principal_Investigator_SSN)
VALUES (1001, 'NSF', '2022-01-01', '2023-12-31', 100000.00, 123456789),
(1002, 'NASA', '2022-03-15', '2023-06-30', 500000.00, 234567890);
INSERT INTO Project_Professor (Project_Number, Professor_SSN, Role)
VALUES (1001, 123456789, 'Principal Investigator'), (1002, 234567890, 'Principal Investigator');
INSERT INTO Project_Graduate (Project_Number, Student_SSN)
VALUES (1001, 345678901), (1002, 456789012);
These SQL queries will create the tables and insert example records into each table based on the given
information.
3. To convert the given table into second normal form (2NF), first we need to convert it to first normal
form (1NF).
Stud_Name Stud_Birth Course_id Course_Name Grade
Liyu 1995 S102 C++ 70
Liyu 1995 IS1205 Web Design 80
Mulatu 1995 S202 Programming 80
Mulatu 1995 IS304 DB 79
Mulatu 1995 IS101 C++ 85
To convert the given table into second normal form (2NF), we need to eliminate any partial
dependencies. Partial dependencies occur when non-key attributes depend on only part of the primary
key. In this case, we have a composite primary key consisting of Stud_Name and Course_id.
To achieve 2NF, we can break down the table into two separate tables: one for student information and
another for course information.
Table 1: Students Table 2: Courses Table 3: Grades
Stud_Name (PK) Course_id (PK) Stud_Name (PK, FK referencing Students)
Stud_Birth Course_Name Course_id (PK, FK referencing Courses)
The Grades table serves as the junction table, connecting the Students and Courses tables. It includes
foreign key references to both primary keys in the respective tables. This design eliminates partial
dependencies and ensures that each attribute depends on the entire primary key.
The modified data in the normalized tables would be as follows:
Table 1: Students Table 2: Courses
Stud_Name Stud_Birth Course_id Course_Name
S102 C++
Liyu 1995
IS1205 Web Design
Mulatu 1995 S202 Programming
IS304 DB
IS101 C++
Table 3: Grades
Stud_Name Course_id Grade
Liyu S102 70
Liyu IS1205 80
Mulatu S202 80
Mulatu IS304 79
Mulatu IS101 85
Now, the data is organized into separate tables, and each attribute depends on the entire primary key in
the respective table. The tables are in second normal form (2NF).
4. a) The SQL equivalent of the relational algebra expression πStu_ID, FName, LName, Stu_Email (σ gpa >
3.5(STUDENT)) is:
SELECT Stu_ID, FName, LName, Stu_Email FROM STUDENT WHERE gpa > 3.5;
b) The relational algebra equivalent of the SQL expression SELECT DISTINCT Emp_ID, Emp_Name,
Address, Salary FROM EMPLOYEE; is:
Π Emp_ID, Emp_Name, Address, Salary (EMPLOYEE)
c) The SQL equivalent of the relational algebra expression that finds Emp_ID, Emp_Name, Address,
Salary of all employees whose salary is greater than or equal to 30,000 is:
SELECT Emp_ID, Emp_Name, Address, Salary FROM EMPLOYEE WHERE Salary >= 30000;
