Review of Forward Engineering Rules
- Where do you create foreign keys for 1:m relationships? Are FK values required?
- Where do you create foreign keys for m:m relationships? Are FK values required?
- Where do you create foreign keys for 1:1 relationships? Are FK values required?
- How do you transform weak entities and associative entities?
Lecture 1: Grammatical Analysis: nouns for objects or attributes and verbs for relationships
Modeling Exercise 1: Insure-A-Person Inc. provides health insurance services to employees and their family members across America. Due to the need to promoting its customer relations, the company has decided to open up a web-based system for clinics and individual customers to be able to file claims on the Internet 24 hours a day and 7 days a week. The company has approached you to design a relational database for that purpose. According to the company, this is how the web-based system is supposed to work. Within 60 days of seeking treatments for himself or any of his family members, a customer needs to logon to the system and file a claim. First, you specify the name of a patient, the date and the place the service was provided, and the primary doctor providing the service. Then, you detail the procedures performed by the doctor. In medical industry, all procedures have been standardized with fixed identification numbers and short descriptions. The insurance company will pay for the service based on all the procedures performed by the service. Please draw an E-R diagram to represent your database design. Write down your assumptions if they are not speculated by the context. Note that the question asks for using advanced modeling constructs such as recursive relationships, weak entities, high-way relationships, super/sub entities, exclusive relationships, etc.
Modeling Exercise 2: In the purchasing department, each purchase request is assigned to a caseworker within the department. This caseworker follows the purchase request through the entire purchasing process and acts as the sole contact person with the person or unit buying the goods or services. The department refers to its fellow employees buying goods and services as “customers.” The purchasing process is such that purchase requests over $1500 must be out for bid to vendors, and the associated Request for Bids for these large requests must be approved by the department. If the purchase is under $1500, the product or service can simply be bought from any approved vendor, but the purchase request must still be approved by the department and they must issue a Purchase Order. For large purchases, once the winning bid is accepted, the department can issue a Purchase Order. List the relevant entities and attributes and draw an ERD for this business.
Additional Modeling Exercises:
- Create an ERD for the following problem using Oracle Data Modeler: BizbyOrder Books is specialized in ordering books for two types of customers: individuals and businesses in lower Manhattan. This is how these two different customers are different. When an individual customer orders books, he or she has to pay 20% down payment. A business customer can establish a credit line with BizbyOrder and pays 50% down if only if the order amount exceeds its credit limit. Business orders often purchase multiple copies of the same title while individual customers do so rarely. Often, when an order containing multiple books is not completely fulfilled, one or more back orders may be created.
- PartDepot wants to develop a system to handle its inventory, products, categories, replacement orders including quantity and discounts, shipments, backorders (when an order is not received completely, a backorder will be created), vendors, bills, and payments. Note that some locations may have multiple branches or stations to take orders. Identify weak entity (or entities), associative entity (entities), and recursive relationship (relationships). Then draw the complete ERD to model the data requirements.
- A store sells raw materials as well as components that are made of other components and raw materials and wants to have a database to keep records of its customers and orders. Here are the specs of their production and sales. 1) A component can be used to make any of several other components; 2) A component can be made of several other components 3) A component can be constructed from several raw materials; 4) A raw material is used in several components; 5) Each component is produced by a single worker; 6) A worker can produce many kinds of components; 7) A component may need a certain quantity of other components and raw materials; 8) There are two kinds of raw materials: perishable ones that have definite expiration time and desirable storage temperature, and non-perishables that does not have expiration time or storage restrictions; 9) a customer may purchase multiple quantities of some components or raw materials and may get volume discount.
- Use Data Modeler to create an ERD to capture the data requirements and business rules. Make up your own attributes for each entity involved.
- Convert the ERD into a relational model
- Write SQL statements to implement your relational model
- Submit both ERD and the relational model along with the SQL code in one file to ecourse.org
Lecture 2: SQL
- Programming Languages: non-procedural and 4th generation
- DDL (Data Definition Language) -- used to create, modify, and delete database objects such as tables, views, accounts, indices, triggers, stored procedures and functions, etc.
- DML (Data Manipulation Language)
- DML Create Table Syntax : A SQL DDL command for creating a new table contains a list of columns and constraints (business rules). For each column, specify column name, data type, and field size and optionally specify whether the column is mandatory (not null) or optional (null), without which, the column will be optional by default. For each constraint, specify constraint name, constraint type (primary key, foreign key, or check), and actual constraint content.
create table XYZ (
column_name data_type (size) [Null or Not Null],
...
constraint constraint_name constraint_type (constraint_content),
...
);
Referential Integrity Rule #1: Data type and size for a foreign key column must match those for the corresponding key column. In other words, the primary key and all its duplications must have matching data type and field size. However, foreign key columns can have different names from the corresponding primary key columns.
Domain Validation and Business Rules: check constraints may be used to either enforce data domains (column values must be in a list of allowed ones) or implement business rules.
Example: Write SQL statements to implement the student enrollment database.
- How can you ensure a business policy on the database server that a student grade must be A, B, C, D, or F.
- How do you enforce the business rule that admission date must be after birth date?
Example 1: DDL Create Table Examples:
- Customer Order Example
- Customer Bank Account Example
- Student registration example
Lecture 3: DBMS: To actually create a database, we need a software program, called DBMS (Database Management System) that lets you create databases and manipulate data in the database. Tasks of DBMS include data organization, data manipulation, and security enforcement:
- Data Organization: defining the database structure according to a selected database model such as the relational model
- Data manipulation: retrieve data, modify data, insert new data, delete data, report, sort data
- Security and Integrity enforcement:
- Provide for the safety of information despite system crashes and power failures
- Prevent unauthorized access
- Concurrency control
Database Management Issues:
- Data redundancy: too many duplicate data items
- Data inconsistency: The same information about one entity appears differently in different places of the database
- Data integrity: Data stored must follow certain rules. For example, data for a balance must be of numerical value. A balance for checking account cannot be a negative value
- Automicity of Transactions: A transaction must be either complete or not at all
- Concurrency Control: No two users can modify the same data item at the same time
- Security: prevent unauthorized access, disk failures, and disasters such as fire, flood, theft
Lecture 4: Oracle
- How to logon to Oracle Server
- How to create, change, and delete Oracle user accounts
Implement the relational model using Oracle
- Database development: create a new table, create fields with data types, and create primary keys and foreign keys with first referential integrity rule
- How to delete tables with or without constraints
- How to list all the tables a user has (meta tables:user_tables)
- how to look up the structure of a particular table
- how to find constraints (meta tables: user_constraints, user_cons_columns)
Lecture 5: SQL DDL for Other Oracle Objects:
How to modify tables without recreate them?
- alter table ... add ...
- alter table ... drop column ..
- alter table .... drop constraint ...
- add table ... modify ...
DDL for Other Database Objects: Users, Tables, Constraints, Sequences, Views, Synonyms, and Database Links (see Chapter 18 for more examples)
DDL for Other Database Objects (Chapter 18)
- Constraints (primary key, foreign key, check)
ALTER TABLE SAMP.DEPARTMENT
ADD CONSTRAINT NEW_UNIQUE UNIQUE (DEPTNO);
ALTER TABLE Cities
DROP CONSTRAINT Cities_PK;
- Sequences (auto number generator)
create sequence seqID start with 1 increment by 3;
- Views (query result saved)
create view Salesman as
select eid, ename, sal, hiredate from emp where job = 'SALESMAN';
# use the following if your Oracle server has TNS name cbo19c.uanet.edu configured
CREATE DATABASE LINK COB19C
CONNECT TO scott IDENTIFIED BY tiger
USING 'cob19c.uanet.edu';
# use the following if your Oracle server does not have TNS name
CREATE DATABASE LINK COB19C
CONNECT TO scott IDENTIFIED BY tiger
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=ism.uakron.edu)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=cob19c.uanet.edu))
)';
Homework:
- Reading:
- Chapter 18 of LIU
- Design:
- Correctness Questions: online
- Closeness Questions: Convert the following ERD into a relational model and write SQL statements to create the tables in the model:
|
