Database management system

Question Three

Assignment 1 Deadline: Day 16 / 02 / 2019 @ 23:59 [Total Mark for this Assignment is 6 ] Database Management Systems IT 344

Instructions: This Assignment must be submitted on Blackboard ( WORD format only ) via the allocated folder. Email submission will not be accepted. You are advised to make your work clear and well-presented , marks may be reduced for poor presentation . This includes filling your information on the cover page. 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 marks being awarded. The work should be your own, copying from students or other resources will result in ZERO marks. Use Times New Roman font for all your answers. Student Details: Name: ### CRN : ### ID: ###

https://www.seu.edu.sa/sites/ar/SitePages/images/logo.png

College of Computing and Informatics

1.5 Marks Learning Outcome(s): LO1: Recognize database file organization and indexing Question One

Compare RAID level 1 and RAID level 5 on the basis of following parameters.

a. Fault Tolerance

b. Performance in terms of read and write operations

c. Suitable application types

1.5 Marks Learning Outcome(s): LO1: Recognize database file organization and indexing Question Two

A file has 40,000 Student records of fixed-length. Consider a disk with block size B=512 bytes. A block pointer is 8 bytes long and a record pointer is 7 bytes long. Each record has the following fields:

Attributes

Size (in bytes)

StudentID

9

Name

30

Sex

1

CourseMajor

10

Phone

9

Address

30

DoB

8

Status

3

a. Calculate the record size R in bytes.

b. Calculate the blocking factor bft and the number of files blocks b assuming an unspanned organization.

c. Suppose the file is ordered by the key field StudentID and we want to construct a primary index on it. Calculate the index blocking factor bft_i.

1.5 Marks Learning Outcome(s): LO3: Develop a standard database using DBMS. LO4: Analyze and optimize algorithms for query processing Question Three

Consider following relational database schema and translate the queries ‘a’ and ‘b’ into relational algebra expressions

Teacher(ID, Name, Designation, Phone, Address)

Course(CourseCode, CourseName, CreditHour)

Taught(TeacherID, CourseCode, Semester)

a. SELECT Name, Address FROM Teacher, Taught

WHERE Teacher.ID=Taught.TeacherID and CourseCode=’IT344’

b. SELECT CourseName FROM Course, Taught

WHERE Course.CourseCode=Taught.CourseCode and TeacherID=200

1.5 Marks Learning Outcome(s): LO4: Analyze and optimize algorithms for query processing Question Four

Draw a query tree for the following relational algebra query.

sid, sname((age=”25″(bid=bid(B x sid=sid)S x R))))

where:

S represents Students (sid, sname, age, email)

B represents Books (bid, bname, auther, edition)

R represents Reserves (sid, bid, start day, end day)