Description
|
3 Marks |
|
Learning Outcome(s): CLO 3,4 Design a database starting from the conceptual design to the implementation of database schemas. Create Entity-Relationship model, Relational model, and write SQL queries.
|
Question One
The Saudi Electronic University (SEU) plans to build a database to manage its academic departments and their staff. Each department employs different types of employees, including full-time professors, part-time lecturers, administrative staff, and student assistants. While all employees share common attributes (such as ID, Name, and Email), each subclass has its own specific attributes:
- Full-Time Professors: Office Number
- Part-Time Lecturers: Weekly Hours
- Administrative Staff: Assigned Tasks
- Student Assistants: GPA Requirement
Without drawing a diagram, clearly specify the components of the Enhanced Entity–Relationship (EER) model based on the above scenario. Your specification must include the following:
- Entities and Attributes
- List all entities and their relevant attributes.
- Specialization/Generalization
- Identify the superclass and subclasses.
- Specify whether the specialization is disjoint or overlapping.
- Specify whether the specialization is total or partial.
- Relationships
- Include a ternary (or n-ary) relationship that is relevant to the context, such as a project involving a full-time professor, a student assistant, and an external sponsor.
|
3 Marks |
|
Learning Outcome(s): CLO 3,4 Design a database starting from the conceptual design to the implementation of database schemas. Create Entity-Relationship model, Relational model, and write SQL queries. |
Question Two
The Saudi Electronic University (SEU) wants to create a basic database to manage students and the courses they register for. Each student has a unique student ID, and each course has a unique course code.
Task:
- Create the SQL Data Definition Language (DDL) statements for the following tables:
- STUDENT
- COURSE
- REGISTRATION (associative table showing which student registered for which course)
- Apply the following rules:
- Each student and course must exist before a registration can be recorded.
- If a student or course is deleted or updated, the change should cascade to the registration table to maintain consistency.
- Include one CHECK constraint to ensure that credit_hours for a course is between 1 and 5.
|
2 Marks |
|
Learning Outcome(s): CLO4 – Design a database starting from the conceptual design to the implementation of database schemas |
Question Three
Consider the following two tables:
Employees Table:
|
EmployeeID |
FirstName |
LastName |
DepartmentID |
Salary |
HireDate |
|
1 |
Salah |
Tariq |
101 |
55000 |
15.03.2020 |
|
2 |
Ahmed |
Muath |
102 |
60000 |
22.07.2018 |
|
3 |
Fadhel |
Mohammed |
103 |
65000 |
10.05.2021 |
|
4 |
Saad |
Nasser |
101 |
70000 |
25.08.2019 |
|
5 |
Khaled |
Ahmed |
102 |
75000 |
11.01.2020 |
Department Table:
|
DepartmentID |
DepartmentName |
|
101 |
HR |
|
102 |
IT |
|
103 |
Marketing |
Task:
Assignment 2
Deadline: 16/04/2025 @ 23:59
[Total Mark for this Assignment is 8]
Student Details:
Name: ###
ID: ###
CRN: ###
Instructions:
• You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on
Blackboard via the allocated folder. These files must not be in compressed format.
• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between
words, hide characters, use different character sets, convert text into image or languages other than English
or any kind of manipulation).
• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the cover
page.
• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.
• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
• Use Times New Roman font for all your answers.
Restricted – مقيد
Question One
Pg. 01
Learning
Outcome(s):
Question One
3 Marks
CLO 3,4
The Saudi Electronic University (SEU) plans to build a database to manage its academic
Design a
database starting
from the
conceptual design
to the
implementation of
database
schemas.
Create EntityRelationship
departments and their staff. Each department employs different types of employees, including
full-time professors, part-time lecturers, administrative staff, and student assistants. While all
employees share common attributes (such as ID, Name, and Email), each subclass has its
own specific attributes:
•
•
•
•
Full-Time Professors: Office Number
Part-Time Lecturers: Weekly Hours
Administrative Staff: Assigned Tasks
Student Assistants: GPA Requirement
Without drawing a diagram, clearly specify the components of the Enhanced Entity–
Relationship (EER) model based on the above scenario. Your specification must include the
following:
model, Relational
model, and write
SQL queries.
Restricted – مقيد
1. Entities and Attributes
o List all entities and their relevant attributes.
2. Specialization/Generalization
o Identify the superclass and subclasses.
o Specify whether the specialization is disjoint or overlapping.
o Specify whether the specialization is total or partial.
3. Relationships
o Include a ternary (or n-ary) relationship that is relevant to the context, such as
a project involving a full-time professor, a student assistant, and an external
sponsor.
Question Two
Pg. 02
Learning
Outcome(s):
CLO 3,4
Question Two
3 Marks
The Saudi Electronic University (SEU) wants to create a basic database to manage students
and the courses they register for. Each student has a unique student ID, and each course has
a unique course code.
Design a
database starting
from the
conceptual design
to the
implementation of
database
schemas.
Create EntityRelationship
model, Relational
model, and write
SQL queries.
Restricted – مقيد
Task:
1. Create the SQL Data Definition Language (DDL) statements for the following tables:
o STUDENT
o COURSE
o REGISTRATION (associative table showing which student registered for which
course)
2. Apply the following rules:
o Each student and course must exist before a registration can be recorded.
o If a student or course is deleted or updated, the change should cascade to
the registration table to maintain consistency.
o Include one CHECK constraint to ensure that credit_hours for a course is
between 1 and 5.
Question Three
Pg. 03
Learning
Outcome(s):
Question Three
2 Marks
Consider the following two tables:
CLO4 – Design a
database starting
Employees Table:
from the
conceptual design
to the
implementation of
database
schemas
EmployeeID
FirstName
LastName
DepartmentID
Salary
HireDate
1
Salah
Tariq
101
55000
15.03.2020
2
Ahmed
Muath
102
60000
22.07.2018
3
Fadhel
Mohammed
103
65000
10.05.2021
4
Saad
Nasser
101
70000
25.08.2019
5
Khaled
Ahmed
102
75000
11.01.2020
Department Table:
DepartmentID
DepartmentName
101
HR
102
IT
103
Marketing
Task:
o
o
o
o
Restricted – مقيد
Write a SQL query to retrieve the names of employees who were hired after January 1,
2020, and earn more than 60,000.
Write a SQL query to list the department names and the highest salary in each
department.
Write a SQL query to update the salary of the employee with EmployeeID = 2 to
65,000.
Write a SQL query to find the number of employees in each department and order the
results by the number of employees in descending order.
Purchase answer to see full
attachment
