CS 4347 — Database Systems, Homework 1

Download PDF

Question 1 - What is the difference between a database schema and a database state? Provide an example to support your answer

The database schema is the fields within each database. The database state is the values stored within each field at a certain time. More simply, the schema is the header rows of the tables (which is mostly fixed), and the state is the other rows of actual data (which vary with respect to time).

Question 2 - What is the difference between physical data independence and logical data independence?

Physical data independence is the ability to change the way data is physically organized (i.e. the schema) without changing the conceptual schema (how the structure of the database is interpreted by the users). Logical data independence is the opposite: It is the ability to change the conceptual schema without changing the way the data is physically organized.

Question 3 - What is the difference between controlled and uncontrolled redundancy? Illustrate with an example

Redundancy refers to storing the same data multiple times. In general, this can slow down the time taken to perform a single update operation. If such redundancy is uncontrolled, and data is stored where it is not strictly necessary, then unnecessary time may be spent in an update of the database. Controlled redundancy refers to redundancy in a controlled manner. Having some redundancy can improve the speed of queries of the database by reducing the amount of indirection required to query the database.

As an example, imagine a database of students. Assume each student schema only has attributes age, name, and ID. Age is not often queried, but is updated regularly. If the age is stored along with every student’s data across the database, then this not only wastes space, but will require huge updates for each student every year. In contrast, only keeping it in a few places makes each update much smaller. The overhead of the indirection required to query for age can be greater, but it also makes updates much cheaper in terms of time and resources to perform.

Question 4 - Describe the three-schema architecture. Why do we need mappings among schema levels?

The three-schema architecture is a separation of the database into three levels. At the highest level, there is the user interface, also known as the “view”. At the lowest level, there is the actual management of how data is stored, also known as the “model”. To coordinate these two levels, the third level (the “controller”) stores rules about what valid data is, validates data, and passes it to the appropriate model.

We need mappings among schema levels because of the differences between the conceptual and physical schema. The mapping is what allows the conceptual schema to be used rather than always having to use the physical schema, similar to using get/set methods in an object oriented programming language rather than directly accessing fields.

Question 5 - Why does the DBMS store the Data definitions of a DB in the Database Catalog?

Storing the data definitions of a Database allows the DBMS to work with other software programs, which may use different conventions. In short, it allows program-data independence.

Question 6 - Softcraft Ltd. is a corporation involved in the design, development, and marketing of software products for a family of advanced personal computers

a

What entities are of interest to such an enterprise?

The items of interest to such an enterprise are:

b

Give a list of these entities and the relationships among them.

c

What are some example queries or updates that might be created for this database?