Solutions for Exercise no. 2

1.3 Explain the difference between physical and logical data independence.

Answer:

• Physical data independence is the ability to modify the physical schema without making it necessary to rewrite the application programs. Such modifications include changing from unblocked to blocked storage, or from sequential to random access files.
• Logical data independence is the the ability to modify the the conceptual schema without making it necessary to rewrite application programs. Such a modification might be adding a field to a record; an application program's view hides the change from the program.

Answer:

1. interaction with the file manager
2. integrity enforcement
3. security enforcement
4. backup and recovery
5. concurrency control

1. No DBMS can do without this, if there is no file manager interaction then nothing stored in the files can be retrieved.
2. Consistency constraints may not be satisfied, account balances could go below the minimum allowed, employees could earn too much overtime (e.g., hours > 80 hours) or, airline pilots may fly more hours than allowed by the law.
3. Unauthorized users may access the database, or users authorized to access part of the database may be able to access parts of the database for which they lack authority. For example, a high school student could get access to national defense secret codes, or employees could find out what their supervisors earn.
4. Data could be lost permanently, rather than at least being available in a consistent state that existed prior to a failure.
5. Consistency constraints may be violated despite proper integrity enforcement in each transaction. For example, incorrect bank balances might be reflected due to simultaneous withdrawals and deposits, an so on.

Answer:

1. Define a model containing all appropriate types of data and data relationships.
2. Define the integrity constraints on the data.
3. Define the conceptual schema for the model.
4. Define the physical level.
5. For each known problem to be solved on a regular basis (e.g., weekly inventory), define a view of the database and write the necessary application programs.
6. Create and initialize the database.

Look at the database that was used in exercise 1. Make a UML class diagram of this mini world (or use the class diagram technique you prefer). In particular pay attention to which attributes that you add to each class, and how classes are associated (object reference and aggregation). Also, sketch the methods for each class. Then answer the following questions.

Q: Does a class correspond to a table?
A: Not exactly. A class specifies both data and methods. A table only stores data.

Q: Does each tuple in a table correspond to an object?
A: Again, not exactly because an object has associated both data and methods, whereas a tuple only stores data. However, you could make an object persistent by storing it as one or more tuples, e.g., by making each stored data item in the object a column in a table. However, this would require writing mapping methods to store and retrieve and objects from disk. These mappings may be non-trivial because an object can contain pointers to other objects and both the pointers and the objects pointed to must be stored in (retrieved from) tables.

Q: Exercise 1 used a file with queries and modifications (insert/delete/updates). Would you map each query and modification to a method?
A: Yes. You could make the queries and modifications a bit more flexible by making each method take parameters, such that you can, e.g., retrieve all employees from the Shoe department and later retrieve all the employees from the Outdoor department simply by calling the method with different parameters.

Q: How would you handle queries from interactive users?
A: That is a problem using, e.g., Java. You execute the same query but specify different parameters, as described above. However, if you want to execute a total different query you have to add a new method to your class.

Q: Can a UML diagram be used for generating a relational schema (tables)?
A: Yes tools exists that can map UML diagrams to tables.