Identify Entities
The first step in designing a database is to identify the ENTITIES which make up the data to be stored. Entities are the NOUNS of the database story. The people, places, things, events, and so on.
Nouns in our story:
· CUSTOMER
· PET
· MEDICATION
· TREATMENT
· VISIT
Notice that most of our entities are things with one event: Visit.
Identify Relationships
After you know what is to be stored in the database, you need to know how those entities relate to each other. RELATIONSHIPS in a database are usually the VERBS of the story. A relationship between two entities will be one of the following:
· ONE to ONE
· ONE to MANY
· MANY to MANY
· NO RELATIONSHIP
Relationships are established by the user of the database to identify and enforce BUSINESS REQUIREMENTS. Let us look at CUSTOMER and PET (first two on AYK 15):
A customer can have many pets but must have at least one.
A pet must be assigned to one and only one customer.
Create ER Diagram
You document the entities and their relationships in a database with an ENTITY-RELATIONSHIP diagram.
On an ER diagram, CUSTOMER and PET would each be a rectangle and the relationship could be a diamond or just an arrow, depending on the methodology you follow.
PET
CUSTOMER
Here, the relationship will always be read two ways, from CUSTOMER to PET and from PET to CUSTOMER, as listed above.
MOST relationships on an ER diagram, when designed in a normalized fashion, will be ONE TO MANY.
You now need to complete the ER diagram for this project. You can draw boxes in MS WORD as I did here, or you can use some other software, or you can draw on paper, then scan or take a picture of it to include in your project submission.
Create Entities in MS Access
Each entity in your database design will become a TABLE in MS Access or other relational database system such as Oracle, MS SQL Server, Libre Office Base, …
Start MS Access and create a blank database. I called mine VET and put it on the Desktop:
Each table in the database should have a PRIMARY KEY and other ATTRIBUTES that describe the entity. MS Access might have started creating a table for you but you can just close it and delete it.
In the CREATE tab, select Table Design. Here is where you define the entity.
We will start with the CUSTOMER entity. This entity, from looking at the project, has a number of fields (attributes). The first one we want to define is the PRIMARY KEY. Type in Customer Number for the Field Name, then make sure that Short Text is the data type. This will allow the user to make up customer numbers which contain letters, numbers, and symbols such as “JH001”. Click on the “Primary Key” icon to mark this field at the PK for the table.
Add in fields for contact information, then the table is done.
Save it and name it CUSTOMER.
Next, create the table for the PET. Notice that the PET will also have a Pet Number. Other fields are identified in the project, but you may come up with more.
NOW, for the CUSTOMER to PET relationship where a PET is owned by one customer, you need to define this relationship in the table by creating a FOREIGN KEY in the PET table that references the CUSTOMER table. The database will then ENFORCE the BUSINESS REQUIREMENTS where a pet is owned by only one customer, and a customer can have many pets. To accomplish this, you create another field in PET, which I am calling CUSTOMER NUMBER:
You must make sure that the data type of CUSTOMER NUMBER in pet is the same as the datatype of CUSTOMER NUMBER in customer.
Create Relationships in MS ACCESS
Now, close the table then in the Database Tools tab, select RELATIONSHIPS. Add the two tables from the pop up box, then continue to the relationships editor. Here, you drag the Customer Number in CUSTOMER onto the Customer Number in PET. In the Edit Relationships box, select the “Enforce Referential Integrity” option. No cascade options are needed. Then create…
Notice, this looks like an ER diagram! It shows the cardinality of the relationship with the 1 and the Many (infinity) on the ends of the relationship line. The database now enforces the two rules for you in that you cannot add a pet if you do not have a customer in the database to own the pet.
Continue creating Tables and updating the Relationships until you have all your entities defined in the database.
Here is my ER from the Relationships editor:
For your project, you can leave off the MEDICATION and PET_MEDS entities. The project indicates that a PET can have many MEDICATIONS. Obviously, a single type of MEDICATION can be prescribed to many different PETs. This is a MANY-To-MANY relationship. In order to implement a M2M in a relational database, you must create a third, intermediary entity. In this case, I created PET_MEDS. This joining entity will contain the PK from each of the other tables, and then any pertinent information specific to that instance of the join. I placed a prescription date there which also becomes part of my primary key for the PET_MEDS table. This lets me prescribe ANTIBIOTIC to the dog JACK01 on more than one date.
DESIGN PROBLEM
This database design still contains a major problem. The problem has to do with the price of treatment and medication. Extra credit for you if you can describe what exactly is the problem.
ENTER DATA
Create your tables and relationships. Then, type in some test data for each of the tables. Include yourself as a customer with one pet that has at least one visit.
REPORT
Now, you need to make the report. The easiest way to accomplish this is to first create a VIEW or QUERY that puts all the data you want into one virtual table (query).
In the Create tab, select Query Design. You can then add the tables you need for your report: Customer, Pet, Visit, Visit Detail, Treatment.
Double click on the fields that you want to have show up in your report.
Close and save the query with a descriptive name. I called mine Invoice Query. You can open the query and see your data there.
Now, select the query then in the Create tab, select Report. This will generate a report with your fields. You can move them around, format them, etc. until it looks good and fits on a page.
Submit your database to the assignment folder!