Description
Question One
Consider the below schema and state of Students Relation from School Database, based on what you learn, identify the problem in the relation ,explain why it should be changed, and propose two possible solutions.
Name SSN SID Date of Birth College Major
Ahmad 12345678 2024001 19-10-1995 IT
Ali 10245789 2024002 1-11-1999 IT
Mohammad 10364325 2024003 15-2-2000 CS
Omar 10267899 2024004 9-10-2003 CS
Ibrahim 10547864 2024005 4-4-1994 IT
Abdulaziz 10443675 2024006 5-6-1997 CS
Ahmad 11086437 2024007 7-8-1999 IT
Student (Name, SSN, SID, Date of Birth, College Major)
Students
*SSN : Social Security Number, SID: Student Identification Number
* for the solution, student should identify the problem , explain why it should be changed, and proposed two different solutions.
Question Two
Consider the below schema and state for Author Relation from Publishing House Database, based on what you learn, explain what is the issue of the relation, and propose a solution for the issue.
Authors (Author ID, Name, Date of Birth , Nationality )
Author ID Name DoB Nationality
10043 Jasim Almotawa’a 1965 Kwaiti
10002 Ghazi AlGusaibi 1940 Saudi
14325 Mustafa Abu Alsaad 1964 Moroccan
19643 Robert Kiyosaki 1947 American
14443 Ahmad Al Hamdan 1992 Saudi
Question Three
You are asked as a database designer to design a database for a new project “Plants and trees care center”. The center is established to repair and fixed damaged plants (the center heals the damaged plants , and does not sell plants).
The center has a number of Plant Specialists where the center information system has store name, ID, Subspecialty of the specialis , years of experience, for every specialist. A person who has a damaged plant or tree (plant owner) register his\her name with contact information e.g. Phone number and email. After the plant owner bring the damaged plant for examination, The information of the every damaged plant or tree is collected, e.g, plant type and name, and the description of the damage. The specialist examines the plant and recommend treatment plan.
Based on the above specification, draw an ER diagram to represent the data requirements as following:
– Identify the main entity types.
– Identify the main relationship types between the entity types.
– Identify attributes and associate them with entity or relationship types.
– Determine primary key attributes for each entity type.
– Illustrate the Cardinality of the entities participating in the relations.
Question Four
List the three main types of constraints in the relational model and explain each in your own words. List the Basic operations for changing the database and explain why the DELETE operation may violate only the referential integrity and not the entity constraint.
Assignment 1
Deadline: Day 18/2/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):
CLO1
Explain the
concepts and
architectures
Question One
1.5 Marks
Consider the below schema and state of Students Relation from School
Database, based on what you learn, identify the problem in the
relation ,explain why it should be changed, and propose two possible solutions.
Student (Name, SSN, SID, Date of Birth, College Major)
involved in the
database
Students
development.
Name
College
Major
SSN
SID
Date of Birth
Ahmad
12345678
2024001
19-10-1995
IT
Ali
10245789
2024002
1-11-1999
IT
Mohammad
10364325
2024003
15-2-2000
CS
Omar
10267899
2024004
9-10-2003
CS
Ibrahim
10547864
2024005
4-4-1994
IT
Abdulaziz
10443675
2024006
5-6-1997
CS
Ahmad
11086437
2024007
7-8-1999
IT
*SSN : Social Security Number, SID: Student Identification Number
Restricted – مقيد
Pg. 02
Question One
* for the solution, student should identify the problem , explain why it should be
changed, and proposed two different solutions.
Restricted – مقيد
Question Two
Pg. 03
Learning
Outcome(s):
CLO1
Question Two
1.5 Marks
Consider the below schema and state for Author Relation from Publishing
House Database, based on what you learn, explain what is the issue of the
relation, and propose a solution for the issue.
Explain the
concepts and
architectures
Authors (Author ID, Name, Date of Birth , Nationality )
involved in the
database
development.
Restricted – مقيد
Author
ID
Name
DoB
Nationality
10043
Jasim Almotawa’a
1965
Kwaiti
10002
Ghazi AlGusaibi
1940
Saudi
14325
Mustafa Abu
Alsaad
1964
Moroccan
19643
Robert Kiyosaki
1947
American
14443
Ahmad Al Hamdan
1992
Saudi
Question Three
Pg. 04
Learning
Outcome(s):
CLO 3,4
Design a
database starting
from the
conceptual design
to the
implementation of
database
schemas.
Question Three
3.5 Marks
You are asked as a database designer to design a database for a new project
“Plants and trees care center”. The center is established to repair and fixed
damaged plants (the center heals the damaged plants , and does not sell
plants).
The center has a number of Plant Specialists where the center information
system has store name, ID, Subspecialty of the specialis , years of experience,
for every specialist. A person who has a damaged plant or tree (plant owner)
register his\her name with contact information e.g. Phone number and email.
After the plant owner bring the damaged plant for examination, The information
Create EntityRelationship
model, Relational
of the every damaged plant or tree is collected, e.g, plant type and name, and
the description of the damage. The specialist examines the plant and
recommend treatment plan.
model, and write
SQL queries.
Based on the above specification, draw an ER diagram to represent the
data requirements as following:
–
Identify the main entity types.
–
Identify the main relationship types between the entity types.
–
Identify attributes and associate them with entity or relationship
types.
–
Determine primary key attributes for each entity type.
–
Illustrate the Cardinality of the entities participating in the
relations.
Restricted – مقيد
Pg. 05
Restricted – مقيد
Question Three
Question Four
Pg. 06
Learning
Outcome(s):
CLO1
Explain the
concepts and
architectures
involved in the
database
development.
Restricted – مقيد
Question Four
1.5 Marks
List the three main types of constraints in the relational model and explain each in your
own words. List the Basic operations for changing the database and explain why the
DELETE operation may violate only the referential integrity and not the entity
constraint.
Purchase answer to see full
attachment
