Reference no: EM132738265
DBT 026 - Database management system
Question ONE
a. Define database management system
b. Name two main components of a relational schema
c. In relational model, attributes are said to be atomic. What is the meaning of atomic?
d. State the main purpose of enforcing different constraints in relational model
e. Give four main advantages of database management system
f. Differentiate between aggregation and differentiation.
g. What is a ternary relationship? Draw ER diagram to illustrate it.
h. Give four main differences between data and information.
i. What is data normalization and what is its main purpose.
Part B
Answer any two questions
Question Two
a. Design an entity relationship diagram of keeping track of information about votes taken in the Kenyan parliament during the last session to amend the interest rate Act. Draw an ER schema diagram for the above application. State clearly any assumption made.
b. A database is being constructed to keep track of the teams and games of a sport league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that games, and the result of the games. Try to design an ER schema diagram for this application, stating any assumption you make. Choose your favorite sport (soccer, baseball, football ...)
Question Three
a. Consider the following relations that keeps track of student enrolment in courses and the books adopted for each course.
STUDENT (San, Name, Major, Bdate)
COURSE(Course#, Cname, Dept)
ENROLL(San, Course#, Quarter, Grade)
BOOK_ADOPTION(Course#, Quarter, Book_isbn)
TEXT(Book_isbn, Book-title, Publisher, Author)
Specify the foreign keys for this schema, stating any assumptions you make.
b. The database below stores students and courses.
STUDENT
|
Name
|
Student_number
|
class
|
Major
|
|
Smith
|
17
|
1
|
CS
|
|
Brown
|
8
|
2
|
CS
|
COURSE
|
Course-name
|
Course-number
|
Credit-hours
|
Department
|
|
Intro to computer science
|
CS1310
|
4
|
CS
|
|
Data structures
|
CS3929
|
4
|
CS
|
|
Discrete mathematics
|
MATH2410
|
3
|
MATH
|
|
Database
|
CS3380
|
3
|
CS
|
SECTION
|
Section-identifier
|
Course-number
|
semester
|
year
|
instructor
|
|
85
|
MATH210
|
Fall
|
07
|
King
|
|
92
|
CS1310
|
Fall
|
07
|
Anderson
|
|
102
|
CS3320
|
Spring
|
08
|
Knuth
|
|
112
|
MATH2410
|
Fall
|
08
|
Chang
|
|
119
|
CS1310
|
Fall
|
08
|
Anderson
|
|
135
|
CS3380
|
Fall
|
08
|
Stone
|
GRADE-REPORT
|
Student-number
|
Section-identifier
|
Grade
|
|
17
|
112
|
B
|
|
17
|
119
|
C
|
|
8
|
85
|
A
|
|
8
|
102
|
B
|
|
8
|
135
|
A
|
PREREQUISITE
|
Course-number
|
Prerequisite-number
|
|
CS3380
|
CS3320
|
|
CS3380
|
MATH2410
|
|
CS3320
|
CS1310
|
Write SQL update statements to do the following on the above database schema
a. Inserts a new student, < ‘Johnson', 25, 1, ‘Math'>, in the database.
b. Change the class student ‘Smith' to 2.
c. Insert new course < ‘Knowledge Engineering', ‘CS4390',3 ‘CS'>.
d. Delete the record for the student whose name is ‘Smith' and whose student number is 17.
c. Using a clearly labeled diagram, describe the three schema architecture or the levels of abstraction in data base management.
Question Four
Draw an E-R diagram for the following situation:
• This is a simplified model for reserving baseball tickets.
• There are teams, which are identified by the team name. Teams are also located in a city.
• Teams play each other in games, which occur on a particular date at a particular time. Games are identified by a game ID, and each game has exactly two teams that play in it.
• A game is played in exactly one stadium.
• A stadium is identified by its name, and is also located in a city.
• Stadiums have seats, which have a section number, a row number, and a seat number.
• Ticket holders reserve seats for a game. Ticket holders are identified by their name.
• Some ticket holders are students (students get discounts, but we are not including that in the model).
b) Using SQL, convert the following ER diagram to the relational model. Hint: You do not need CHECK constraints.
Question Five
a. Define domain constraint
b. List and explain five types of key used in relational models.
c. Explain the four main functions of a database management system
d. List five ways of maintaining data integrity