Chapter 9
Describe the steps used to transform a domain class diagram into a relational database schema.
1. Create a table for each class in the domain model. 2. Choose a primary key for each table (invent one, if necessary). 3. Add foreign keys to represent one-to-many associations. 4. Create new tables to represent many-to-many associations. 5. Represent and adjust classification hierarchies. 6. Define referential integrity constraints. 7. Evaluate schema quality and make necessary improvements. 8. Choose appropriate data types. 9. Incorporate integrity and security controls.
Database vs. Database Management System (DBMS)
> an integrated collection of stored data that is centrally managed and controlled. > A DBMS is a system software component that is generally purchased and installed separately from other system software components (e.g., operating systems). Examples of modern database management systems include Microsoft SQL ServerTM, Oracle, and MySQLTM.
With respect to relational databases, briefly define the terms row and attribute value.
> one horizontal group of data attribute values in a table. > the value held in a single table cell
What is the difference between a shared lock and an exclusive lock?
A shared lock permits reading; an exclusive lock does not.
When should database design be performed? Can the database be designed iteratively or must the entire database be designed at once?
ASAP. Iteratively, as data types and attributes can change frequently during development.
What are the advantages and disadvantages of replicated databases?
Advantages: independence, quick queries Disadvantages: increased storage, high maintenance
Many-to-many associations - Add the primary key attribute(s) of the "one" class to the table that represents the "many" class
False
Why are databases the preferred method of storing data used by an information system?
In addition to replacing or upgrading existing data in the database itself, it is also common that the previous system also fed data to other information systems in the organization.
What types of data (or attributes) should never be stored more than once in a relational database? What types of data (or attributes) usually must be stored more than once in a relational database?
Non-key data. Primary keys.
Describe the process of relational database normalization. Which normal forms rely on the definition of functional dependency?
See image for process. 2nd and 3rd.
What are the responsibilities of a data administrator? What are the responsibilities of a database administrator?
The person in charge of the structure and integrity of the data vs The person in charge of the safety and operation of the DBMS
An exclusive lock is also known as a write lock.
True
One to many association - Add the primary key attribute(s) of the "one" class to the table that represents the "many" class
True
What is a heterogeneous database?
a database distributed across multiple locations with different DBMSs and with local access allowed without global schema coordination
What is a homogeneous database?
a database distributed across multiple locations with the same DBMS, and all database access coordinated by a global schema
What is relational database normalization? Why is a database schema in third normal form considered to be of higher quality than a non- normalized database schema?
a formal technique for transforming a relational schema to an equivalent one that minimizes data redundancy and eliminates data anomalies. First Normal Form (1NF) - a restriction that all fields must be atomic, or single valued Second Normal Form (2NF) - a restriction that a table is in 1NF and that each non-key attribute is functionally dependent on the entire primary key Third Normal Form - a restriction that a table is in 2NF and that no non-key attribute is functionally dependent on any other non-key attribute
What is a transaction with regard to updating a database?
a piece of work with several steps that must all be completed to be valid
What does SQL stand for and what is its purpose?
a query language used to access and update the data in a relational database
What is functional dependency?
a relationship between columns such that the values in one (or more) column determine the values in a second column (or set of columns)
What is a foreign key? Why are foreign keys used or required in a relational database? Are duplicate foreign key values allowed? Why or why not?
an attribute that duplicates the primary key of a different (or foreign) table
What is the difference between a primitive data type and a complex data type?
data types supported directly by computer hardware or a programming language vs data types supported directly by computer hardware or a programming language
What is a database schema? What information does it contain?
database component that contains descriptive information about the data stored in the physical data store
What is the purpose of database synchronization for a replicated database?
here
Which would be easier to configure and maintain? A vertically partitioned database or a horizontally partitioned database? Explain why.
horizontally partitioned database are simpler.
What is a primary key? Are duplicate primary key values allowed? Why or why not?
the key chosen by a database designer to define relationships by being used as a foreign key in other tables
What is referential integrity?
the principle that every value as a foreign key in one table must have an equivalent value as the primary key in the associated table
What is the basic purpose of transaction logging?
to create an audit trail of all database updates and, therefore, track any errors or problems that occur
