Description
College of Computing and Informatics
Assignment 1
Deadline: Sunday 14/07/2024 @ 23:59
[Total Mark for this Assignment is 15]
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):3
Question One
Create Entity-
Library Management System
Relationship
Scenario: You are tasked with designing a database for a library management
model, Relational
model, and write
SQL queries.
3 Marks
system. The system needs to track books, authors, members, and book loans.
The requirements are as follows:
•
Each book has a unique ISBN, title, publication year, and genre. A book
can have multiple authors.
•
Each author has a unique author ID, name, and nationality.
•
Each member has a unique member ID, name, address, and
membership date.
•
Each loan records the loan of a book to a member and includes the loan
date and return date.
•
Authors can write multiple books, and a book can have multiple authors.
•
Members can borrow multiple books, but a book can only be borrowed
by one member at a time.
Given the scenario above, design the relational schema for the library
management system. Identify the tables, their attributes, primary keys, and
foreign keys. Explain the constraints you would apply to ensure data integrity.
Restricted – مقيد
Question Two
Pg. 02
Learning
Outcome(s):3
Question Two
3 Marks
Based on the library management system scenario, create an Entity-
Create Entity-
Relationship (ER) diagram. Include entities, relationships, attributes, and keys.
Relationship
Explain the design choices for the relationships and any constraints applied.
model, Relational
model, and write
SQL queries.
Restricted – مقيد
Question Three
Pg. 03
Learning
Outcome(s):3
Question Three
3 Marks
Extend the ER diagram created in Module 4 to include subclass and
Create Entity-
superclass relationships. Specifically, include subclasses for different types of
Relationship
books (e.g., Fiction and Non-Fiction) and different types of members (e.g.,
model, Relational
Student and Faculty). Explain the rationale behind your design choices.
model, and write
SQL queries.
Restricted – مقيد
Question Four
Pg. 04
Learning
Outcome(s): 4
Question Four
3 Marks
Based on the given ERD, create two tables one for the relations metadate and
Design a
the other for the columns metadata (for TransactionDetail, MsFish, and
database starting
MsFishType) in a simplified database catalog.
from the
conceptual design
to the
implementation of
database
schemas.
Restricted – مقيد
Question Five
Pg. 05
Learning
Outcome(s): 1
Question Five
3 Marks
Using your own words, explain the advantages of the Three-Tier Client-Server
Explain the
concepts and
architectures
involved in the
database
development.
Restricted – مقيد
Architecture over the Three-Tier Architecture in terms of security, scalability,
and data integrity.
.
Assignment 1
Deadline: Sunday 14/07/2024 @ 23:59
[Total Mark for this Assignment is 15]
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):3
Question One
Create Entity-
Library Management System
Relationship
Scenario: You are tasked with designing a database for a library management
model, Relational
model, and write
SQL queries.
3 Marks
system. The system needs to track books, authors, members, and book loans.
The requirements are as follows:
•
Each book has a unique ISBN, title, publication year, and genre. A book
can have multiple authors.
•
Each author has a unique author ID, name, and nationality.
•
Each member has a unique member ID, name, address, and
membership date.
•
Each loan records the loan of a book to a member and includes the loan
date and return date.
•
Authors can write multiple books, and a book can have multiple authors.
•
Members can borrow multiple books, but a book can only be borrowed
by one member at a time.
Given the scenario above, design the relational schema for the library
management system. Identify the tables, their attributes, primary keys, and
foreign keys. Explain the constraints you would apply to ensure data integrity.
Restricted – مقيد
Question Two
Pg. 02
Learning
Outcome(s):3
Question Two
3 Marks
Based on the library management system scenario, create an Entity-
Create Entity-
Relationship (ER) diagram. Include entities, relationships, attributes, and keys.
Relationship
Explain the design choices for the relationships and any constraints applied.
model, Relational
model, and write
SQL queries.
Restricted – مقيد
Question Three
Pg. 03
Learning
Outcome(s):3
Question Three
3 Marks
Extend the ER diagram created in Module 4 to include subclass and
Create Entity-
superclass relationships. Specifically, include subclasses for different types of
Relationship
books (e.g., Fiction and Non-Fiction) and different types of members (e.g.,
model, Relational
Student and Faculty). Explain the rationale behind your design choices.
model, and write
SQL queries.
Restricted – مقيد
Question Four
Pg. 04
Learning
Outcome(s): 4
Question Four
3 Marks
Based on the given ERD, create two tables one for the relations metadate and
Design a
the other for the columns metadata (for TransactionDetail, MsFish, and
database starting
MsFishType) in a simplified database catalog.
from the
conceptual design
to the
implementation of
database
schemas.
Restricted – مقيد
Question Five
Pg. 05
Learning
Outcome(s): 1
Question Five
3 Marks
Using your own words, explain the advantages of the Three-Tier Client-Server
Explain the
concepts and
architectures
involved in the
database
development.
Restricted – مقيد
Architecture over the Three-Tier Architecture in terms of security, scalability,
and data integrity.
.
Purchase answer to see full
attachment
