Database system concepts and architecture

a. Design a simple database schema with not more than 4 files for a University database system indicating all applicable constraints and information. Also, show a sample database state for the database.

Answer:

`Students take courses’ database schema is :
Student (stuid : integer, sname : string, major : string, gpa : real)
Take (stuid : integer, cid : string, grade : integer)
Course (cid : string, ctitle : string)
Some constraints are :
A student can take many courses.
A course can be taken by many students.
A state of this database is :
Student
Stuid sname major gpa
11 John Smith CS 80
22 Mary Cane Math 67

Take
Stuid     cid    grade
11      60-140    60
11      60-100    70
11      62-120    75
22      62-140    80
22      40-140    90

Course
cid       ctitle
60-140    Programming
60-100    Computer Concepts
62-120    Algebra
62-140    Calculus I
40-140    Comm Studies Intro

b. Using your database, describe the difference between logical and physical data independence.

Answer:

Logical data independence:
i. The ability to change the conceptual schema (e.g. get names of students 
with GPA>85%; and get names and address of students in year 2) without having 
to change the external schema or application program (eg. when student has
 an additional attribute “address”).

ii. Physical data independence: It has the ability to change the internal 
schema (e.g., storage model like store the files as B-tree instead of arrays)
 without having to change the conceptual schema such as relations.
For example an access path (such as B-tree) to improve retrieval speed of
 TAKE file records should not require the TAKE relation or file or its query
 to be altered much. An example query on TAKE is “list all courses taken by each student”.