Lecture 4

improving the design
the table structures are cleaned up to eliminate the troublesome partial and transitive dependencies. you can now focus on improving the database’s ability to provide information and on enhancing its operational characteristics. in the next few paragraphs, you will learn about the various types of issues you need to address to produce a good normalized set of tables.
evaluate pk assignments
each time a new employee is entered into the employee table, a job_class value must be entered. unfortunately, it is too easy to make data-entry errors that lead to referential integrity violations. for example, entering db designer instead of database designer for the job_class attribute in the employee table will trigger such a violation. therefore, it would be better to add a job_code attribute to create a unique identifier. the addition of a job_code attribute produces the dependency:
job_code —> job_class, chg_hour
evaluate naming conventions
it is best to adhere to the naming conventions. therefore, chg _hour will be changed to job_ chg _hour to indicate its association with the job table. in addition, the attribute name job_class does not quite describe entries such as systems analyst, database designer, and so on the label job_descriptlon fits the entries better. also, you might have noticed that hours was changed to assign_hours in the conversion from 1nf to 2nf. that change lets you associate the hours worked with the assignment table.
refine attribute atomicity
it is generally good practice to pay attention to the atomicity requirement. an atomic attribute is one that cannot be further subdivided. such an attribute is said to display atomicity. clearly, the use of the emp_name in the employee table is not atomic because emp_name can be decomposed into a last name, a first name, and an initial.
identify new attributes
if the employee table were used in a real-world environment, several other attributes would have to be added. for example, an employee hire date attribute (emp_hiredate) could be used to track an employee’s job longevity and serve as a basis for awarding bonuses to long-term employees and for other morale-enhancing measures.
identify new relationships
according to the original report, the users need to track which employee is acting as the manager of each project. this can be implemented as a relationship between employee and project.
what is a transaction?
database transactions reflect real-world transactions that are triggered by events such as buying a product, registering for a course, or making a deposit in a checking account. transactions are likely to contain many parts. for example, a sales transaction might require updating the customer’s account, adjusting the product inventory, and updating the seller’s accounts receivable. all parts of a transaction must be successfully completed to prevent data integrity problems. therefore, executing and managing transactions are important database system activities. to understand the concept of a transaction, suppose that you sell a product to a customer. furthermore, suppose that the customer may charge the purchase to his or her account. given that scenario, your sales transaction consists of at least the following parts
• you must write a new customer invoice.
• you must reduce the quantity on hand in the product’s inventory.
• you must updating the account transactions.
• you must updating the customer balance.
the preceding sales transaction must be reflected in the database. in database terms, a transaction is any action that reads from and/or writes to a database. a transaction may consist of a simple select statement to generate a list of table contents it may consist of a series of related updating statements to change the values of attributes in various tables it may consist of a series of insert statements to add rows to one or more tables, or it may consist of a combination of select, updating, and insert statements. the sales transaction example includes a combination of insert and updating statements. given the preceding discussion, you can now augment the definition of a transaction. a transaction is a logical unit of work that must be entirely completed or entirely aborted no intermediate states are acceptable. in other words, a multi-component transaction, such as the previously mentioned sale, must not be partially completed. updating only the inventory or only the accounts receivable is not acceptable. all of the sql statements in the transaction must be completed successfully. if any of the sql statements fail, the entire transaction is rolled back to the original database state that existed before the transaction started. a successful transaction changes the database from one consistent state to another.
a consistent database state is one in which all data integrity constraints are satisfied. to ensure consistency of the database, every transaction must begin with the database in a known consistent state. if the database is not in a consistent state, the transaction will yield an inconsistent database that violates its integrity and business rules. for that reason, subject to limitations discussed later, all transactions are controlled and executed by the dbms to guarantee database integrity. most real-world database transactions are formed by two or more database requests.
a database request is the equivalent of a single sql statement in an application program or transaction. for example, if a transaction is composed of two updating statements and one insert statement, the transaction uses three database requests. in turn, each database request generates several input/ output (i/o) operations that read from or write to physical storage media.