Concept of Database:
- Database is a set of records well organized using a particular logical data model
- Records: A records is an array of observed characteristics (or attributes) on one object (entity)
- Well Organized: 1) related records are connected; 2) no unnecessary redundancy
- Logical Data Models: Hierarchical, Network, Relational
- Database Development Process
- Data Modeling: Entity, Attribute, relationships
- Database Implementation: DBMS
CASE Tool: Oracle Data Modeler
- Terminology Confusion: logical model = conceptual model, relational model = logical model
- How to draw entities, create attributes, and refine relationships using Oracle Data Modeler
Data Modeling: Identify Objects, Attributes, and Relationships
- Identify Objects: (1) objects can be tangible such as a customer or intangible such as an account or meeting; (2) objects must be observable at an instant in time; (3) objects must have multiple attributes (or dimensions) to be observed.
- Identify Attributes: (1) attributes must be relevant to the problem domain; (2) attributes cannot be foreign; and (3) attributes must be singled-valued, not composite or multi-valued.
- Identify Relationships: (1) A relationship reflects a verb action that link two objects; (2) A relationship allows one to navigate from one record to related records; and (3) if data on verb action needs to recorded, then action records cuts in the middle between original two objects.
Example 1: (Bank System) Customers own Accounts.
Example 2: A professor wants to keep data for all his books, publishers, and authors so that, whenever he has comments, he can communicate with the authors using US Mail, phone, or email. He also like to have data about the publishers because he may need to go to the company’s web site or call the company to look for more information.
Example 3: A student club wants to have a database to manage its data on members. The club assigns members to its numerous committees. It is possible that one member can serve in more than one committee and will chair at most one committee. Each year the club organizes many events. Each time, the club delegates one committee to be in charge of an event. Build an ERD to model the club’s business objects
Example 4: National Parks Association (NPA) wants to track the attacks on visitors by animals in the thousands of parks across the country. For each incident, the name and address of the person is recorded, along with the type of animal that attacked, the date of the attack, and the location of the attack. Create an ERD to capture the data requirements for NPA. (Think: should animal data be recorded? Do animals have IDs?)
Identify Relationships: Employees manage accounts: Are employees assigned to manage certain accounts, e.g., Lisa is assigned to manager Good Year and Firestone accounts, Bill manages Apple and Microsoft accounts, etc. If yes, connect Employee to Account directly. If no, if every employee can manage any accounts but we just need to have record on the historical action of account management, such opening, closing, or update accounts, then we should create an intermediate entity, termed Transactions, Operations, MaintenanceRecords, etc. and connect Employee to Account indirectly: Employee --- Transaction --- Account
Forward Engineering: ERD to Relational Model (Remember the these rules, more advanced rules are seen in Chapter 6 of LIU)
- Each entity set is converted into a table with its attributes as the columns of the table
- Depending on the mapping cardinality, you modify the tables to make them related
- If the relationship is 1:m or m:1, you put the primary key of the table for one-side entity set into the table for the many-side entity set
- If the relationship is m:m, you build a junction table which has the primary keys of both entity sets as columns
- If the relationship is 1:1, you can put the primary key of one entity set into the table for another entity set.
- Foreign keys are optional if and only the receiving side is optional. Foreign keys in a junction table is always required.
Example 2: Convert the above ERD into the relational model
Writing Assignments:
- Reading Assignments: Chapter 2 of LIU and the Rise of Relational Database (online at ecourse.org).
- Correctness Questions: online at course.org
- Hands-on Questions: ABC Purchasing Office needs a database for its supplies so that internal departments and employees can checkout them online. The office orders and receives supplies from various vendors. The actual cost of each item is billed to the departments who use the supplies. As a convention of organizing inventories, supplies are organized into categories. For each supply, the maximum and minimum inventory levels are kept so that when the stock of a part is below the minimum, an order will be issued to get it refilled. Construct an ERD for this system using Oracle Data Modeler.
|
