ASSIGNMENT

SEPTEMBER 2022 SEMESTER

SUBJECT CODE : CDS300

SUBJECT TITLE : DATABASE SYSTEMS

LEVEL : BACHELOR

STUDENT’S NAME : Qonita Qotrunnada

MATRIC NO. :

PROGRAMME : BICT (HONS)

ACADEMIC :
FACILITATOR

LEARNING CENTRE :

INSTRUCTIONS TO STUDENTS

1) This assignment consists of FIVE (5) questions. Answer ALL questions.

2) Plagiarism in all forms is forbidden. Students who submit plagiarised assignment will be
penalised.

3) Your assignment will be examined based on the followings

 a complete working solution.
 ability of using methods available in the learning materials.

4) This assignment carries a 60% weightage toward final grade.

THERE ARE SIX [6] PAGES OF QUESTIONS, INCLUDING THIS PAGE.

INSTRUCTION. Answer ALL questions. [Total : 60 Marks]

Question 1

Refer Figure 1.

Figure 1: Database System Environment

a) Briefly explain the following functions of DBMS software as shown in Figure 1:

i. Software to process queries/programs

Note: You may include the definition of “query” in your explanation

Answer:

Query is a command that has the ability to set a data that must be
displayed or the data that is displayed as desired. In addition, the query is
the syntax or command that is used so that we can interact between the
query in the database

ii. Software to access stored data

Answer:

Database software is software that is used to create and manage database

structures (databases) and to access data. Database software is also
commonly referred to as a database management system.
 [4 Marks]

b) Define Stored Database Definition (Meta-Data) and provide ONE (1) example.

Answer:

Metadata is a collection of data that can be created manually or with the help
of tools. If the data is in the form of text, the metadata example will be the
field name, length, and the field type which is integer, character, date.

[6 Marks]

[Total : 10 Marks]

Question 2

Given read/write conflict scenarios in Figure 2:

Figure 2 : Read/Write Conflict Scenarios

a) Briefly explain the scenario of T1 and T2 in Figure 2.

[5 Marks]

b) Locking methods can be applied in the scenario. Briefly explain what will happen to
T2 if the method is applied.

[5 Marks]
[Total : 10 Marks]

Question 3
Consider the following relations for a database that keeps track of student enrollment in
courses and the books adopted for each course:

STUDENT(SSN, Name, Major, Bdate)

COURSE(Course#, Cname, Dept)

ENROLL(SSN, Course#, Quarter, Grade)

BOOK_ADOPTION(Course#, Quarter, Book_ISBN)

TEXT (Book_ISBN, Book_Title, Publisher, Author)

Specify the foreign keys for this schema and state any assumptions that you make.

Answer:

Foreign key list:

1. ‘SSN’ in the Enroll entity

2. ‘Course#’ in the Enroll entity

3. ‘Quarter’ in the Book Adoption entity

4. ‘Book_ISBN’ in the Text entity

My asumptions is Student entity and Course entity is a Regular entity (Strong type
entity) because independent of any other entity in a schema, it does not have
foreign key, just primary key. The primary key in Student entity is ‘SSN’, The
Student entity is a parent entity of Enroll entity. The primary key in Course entity is
‘Course#’, the Course entity is a parent entity of Enroll entity and Book Adoption
entity.

Enroll entity is a entity that has 2 Foreign key and 1 primary key. The Foreign key
is ‘SSN’ from Student entity and ‘Course#’ from Course entity. The Primary key is
‘Quarter’, it’s a parent entity of Book Adoption entity.

Book Adoption entity is a entity that has 2 Foreign key and 1 primary key. The
Foreign key is ‘Course# from Course entity and ‘Quarter’ from Enroll entity. The
Primary key is ‘Book_ISBN’, it’s a parent entity of Text entity.
Text entity is a weak entity (child entity type) because it does not have primary key,
just foreign key, Text entity is depend on Book Adoption entity

[10 Marks]

Question 4

An Entity Relationship Diagram (ERD) shows the relationships of entity sets stored in a
database. Given a statement below:

In a university, a Student enrolls in Courses. A student must be assigned to at least one or

more Courses. Each course is taught by a single Professor. To maintain instruction quality,
a Professor can deliver only one course.

Draw a suitable ER-diagram with all possible entities, attributes, cardinality and
relationships. Show step by step in creating the ER-diagram by listing and briefly explaining
the following:

 Entities

 Relationships

 Cardinality identification

Answer:

- Identify the entities

We already have three entities Professor, Course, and Student, in response to

the query

- Establish the relationship

The relationship between the two is as follows:

We have the following relationship, as stated in the question,

1. Professor teaches course

2. Student enrolls in course

- Cardinality Identifier

The cardinality is, in accordance with the query:

- One-to-one instruction is the only course he professor offers

- Students may enroll in multiple courses (One to Many)

[15 Marks]

Question 5

This is a work order (Figure 3) used by a sub-contracting company that performs tile and
other hard surface installations.
 Work Order Status
Work Order W1000 Date Issued: 27 March 2022
No:
Customer No: C123
Customer Crepes-to-Go
Billing: Name Corporate
Address: 778 Main Street
Seattle, WA 98107
Work Crepes-to-Go Store 1
Location: 4792 University Ave
Name Seattle, WA 98107
Address:

Notes: 03/31/2022: Problems with toxic mold on the existing sub-floor. Need to
have inspection by qualified mold specialist – James Milford. Inspection
scheduled 4/3/022.
Task Square Est. Actual Status Date Date Completed
Feet Hours Hours Started
Remove 250 5 6 Completed 03/28/022 03/28/022
existing
floor
Remove mold 250 10 3 Waiting for 03/31/022
inspection
Prepare sub 275 20 Pending
floor
Tile 275 12 Pending
Installation
Grouting 275 6 Pending
Seal and 275 6 Pending
finish work

Employee : Mary Manager Date Required: 25 April 2022

Manager

Figure 3
 Other information about the application:

 There is one customer per work order. There is one work location per work order.
As shown on the sample work order, it is possible that the address of the customer
may be different than the address of the work location.
 There may be one or many tasks per work order.
 One employee serves as the manager for a given work order, but the company has
many employees who have the potential to be managers for work orders.
 The “status” column for a given task is a “standardized” comment. The company
has about 30 different potential “status” comments that are used.

a) Convert the above form into a single table

[5 Marks]

b) Normalize the table into 3NF form. Show your normalization steps in details because
marks will be given based on the steps shown. State your assumption if needed.

Answer:

[10 Marks]

[Total : 15 Marks]
END OF ASSIGNMENT QUESTIONS