Assignment IT-344 Database

profilesmartman1212
IT344Assignment1-Solution.docx
Home>Human Resource Management homework help>Assignment IT-344 Database

Pg. 02

Classification: Internal Use

خطأ! استخدم علامة التبويب "الصفحة الرئيسية" لتطبيق Heading 1 على النص الذي ترغب في أن يظهر هنا.

Assignment 1

Deadline: Day 16/02/2019 @ 23:59

Database Management Systems

IT 344

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

Classification: Internal Use

College of Computing and Informatics

Classification: Internal Use

Question One

1.5 Marks

Learning Outcome(s):

LO1: Recognize database file organization and indexing

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

RAID 5 Parity with striping

RAID 1 Mirroring

RAID 5 is the most common secure RAID level. It requires at least 3 drives but can work with up to 16. Data blocks are striped across the drives and on one drive a parity check sum of all the block data is written. The parity data is not written to a fixed drive, they are spread across all drives, as the diagram shows. Using the parity data, the server can recalculate the data of one of the other data blocks, should that data no longer be available. That means a RAID 5 array can withstand a single drive failure without losing data. Although RAID 5 can be achieved in software, a hardware controller is recommended. Often extra cache memory is used on these controllers to improve the write performance

RAID 1 has several advantages over RAID 0. In RAID 1, data is stored in two locations, or sets of data drives. The first location is the "live" dataset. The second is a mirror drive (or set of drives) . If a drive fails, the controller uses either the data drive or the mirror drive for data recovery and continues operation. You need at least 2 drives for a RAID 1 array

Definition

C:\Users\Instructor\AppData\Local\Microsoft\Windows\INetCache\Content.Word\blog-raid5.png

blog-raid1

· Drive failures have an effect on throughput, although this is still acceptable.

· This is complex technology. If one of the disks in an array using 4TB disks fails and is replaced, restoring the data (the rebuild time) may take a day or longer, depending on the load on the array and the speed of the controller. If another disk goes bad during that time, data is lost forever.

· The main disadvantage is that the effective storage capacity is only half of the total drive capacity because all data gets written twice.

· Software RAID 1 solutions do not always allow a hot swap of a failed drive (meaning it cannot be replaced while the server keeps running). Ideally a hardware controller needs to be used in order for a replacement drive to be put online while the server is online.

Fault Tolerance

· Read data transactions are very fast while write data transactions are somewhat slower (due to the parity that has to be calculated).

· If a drive fails, you still have access to all data, even while the failed drive is being replaced and the storage controller rebuilds the data on the new drive.

· RAID 1 offers excellent read speed and a write-speed that is comparable to that of a single drive.

· In case a drive fails, data doesn't have to be repaired. Depending on the configuration, a drive replacement can rectify the problem and you're back online with minimal involvement.

Performance in terms of read and write operations

RAID 5 is a good all-round system that combines efficient storage with excellent security and decent performance. It is ideal for file and application servers that have a limited number of data drives.

RAID 1 is ideal for mission critical storage, for instance for web servers, or servers with small chassis where only 2 drives can be used. This is especially valuable if data on these systems doesn't change often, so restoration of the data from a backup is not very complex.

Apps

Question Two

1.5Marks

Learning Outcome(s):

LO1: Recognize database file organization and indexing

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.

Record Length R=9+30+1+10+9+30+8+3=100

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

Blocking factor bfr = floor (B/R) = floor(512/100) = 5 records per block

Number of blocks needed for file = ceiling(r/bfr) = ceiling(40000/5) = 8000

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.

Index record size R i = (V SSN + P) = (9 + 8) = 17 bytes

Index blocking factor bft_i =floor(B/R i ) = floor(512/17) = 30

Question Three

1.5Marks

Learning Outcome(s):

LO3: Develop a standard database using DBMS.

LO4: Analyze and optimize algorithms for query processing

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’

Name,Address (σTaught.CourseCode="IT344"(Teacher⋈Taught))

b. SELECT CourseName FROM Course, Taught

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

CourseName (σTeacher.ID=200(Course⋈Taught))

Question Four

1.5 Marks

Learning Outcome(s):

LO4: Analyze and optimize algorithms for query processing

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)

SID, SNAME

AGE="25"

BID=BID

X

SID=SID

X

Students

Reserves

Books