Solutions for Exercise no. 2
From "Database System Concepts"
1.3 Explain the difference between physical and logical data independence.
1.4 List five responsibilities of the database manager. For each responsibility,
explain the problems that would arise if the responsibility where not discharged
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.
If these responsibilities were not met by a given DBM the following problems
can occur, respectively
interaction with the file manager
backup and recovery
1.7 List six major steps that you would take in setting up a database
for a particular enterprise.
No DBMS can do without this, if there is no file manager interaction then
nothing stored in the files can be retrieved.
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
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
Data could be lost permanently, rather than at least being available in
a consistent state that existed prior to a failure.
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.
Define a model containing all appropriate types of data and data relationships.
Define the integrity constraints on the data.
Define the conceptual schema for the model.
Define the physical level.
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.
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
A: Yes tools exists that can map UML diagrams to tables.