Question

This database experience project has two parts: (1) creating the presentation-layer ERD, (2) developing the semantic integrity constraints (meta-data) for your data. The deliverable will include a title page with your name, your client’s name, and all sections as part of one file (.pdf).

1. Create the ERD

Review the business rules described in your project files and create a presentation-layer ERD illustrating the entities/relationships needed to support your database design with accompanying data definitions. Use any graphical tool you prefer but be consistent. No gridlines – keep it clean.

Make sure to follow all rules of ER diagramming. If you see additional files (e.g., patient visit form, initial medical history form), you need to consider including the data from those forms into your database model as well. Include all entities, label relationships if there are multiple relationships between 2 entities, include all attributes and constraints where necessary and identify all primary and foreign keys as discussed.

For full credit, part 1 deliverable must include:

1. ERD incorporating all entities identified in the project business rules (see your project specifics)
2. Relationships between those entities are drawn with min/max cardinalities and constraints where required. All primary and foreign keys will be identified.
3. Do not break the rules of EER diagramming.

2. Create Semantic Integrity Constraints

Semantic Integrity Constraints (or Data definitions) must be in the form of a table (use format shown below – do not add any other info columns), defining briefly each attribute of each entity, for each table. For example:

ENTITY	Attribute	Definition
STUDENT	StudentID	12-digit number uniquely identifying the student, assigned by UH, not based on SSN or other ID

STUDENT	FirstName	(required), character data, max-length 30
STUDENT	MI	Middle initial (optional), max-length 1
STUDENT	LastName	(required), character data, max-length 30
STUDENT	AcctBalance	Outstanding balance owed to the university for tuition, fees, etc. New registrants start at $0.00; 2 decimal places
GRADES	StudentID	12-digit number uniquely identifying the student, assigned by UH, not based on SSN or other ID

Data definitions for all attributes must be included; data-type and max length as a minimum requirement. (if the attribute is obvious such as first name, no need to define as “first name” etc.)

Answer 1

Though analysts mostly employ the use of ERDs for communicating the design of databases to developers, they are also an interesting way of illustrating the constraints that guide the use of a system to users through cardinality. For example, an ERD can show how many orders a customer can have or whether one order can only be linked to only one or multiple customers. Because it shows the relationship between the entities in the system, it may also be used for explaining constraints or business rules.