Fundamentals of databases : Conceptual data models and entity relationship modelling
What does the specification say?
- Produce a data model from given data requirements for a simple scenario involving multiple entities.
- Produce entity relationship diagrams representing a data model and entity descriptions in the form: Entity1 (Attribute1, Attribute2, .... ).
Databases will probably store data about a variety of things. For example, if we look at the tables required for an online store:
These tables could sit on their own, but when we start to describe each of them we notice that they are related to each other. For example:
- The Receipt table records which product a customer has bought, and which date it was purchased on.
This describes the Receipt record, and looking at its structure we see that the primary keys from other tables are included in it.
Receipt(CustomerID, ProductID, DateTime, Total, StaffID)
In other words we could say:
A receipt has one customer A customer can have many receipts A receipt has one product A product can be part of many receipts A receipt was created by one staff member A staff member can create many receipts
We can then draw this diagram like so:
To link tables together and allow for us to query a database we use relationships. There are three types of relationship that you need to know. Each is shown below using 'crows foot notation' which is one of many ways to describe these relationships:
|Many to Many||An Author can write several Books, and a Book can be written by several Authors|
|One to Many||A Biological Mother can have many children, and a child can have only one Biological Mother|
|One to One||A Country has only one Capital City, and a Capital City is in only one Country|
An animal has one genome
A student has many classes
A league has many teams
vehicle ---< wheel
Father >--< Child (unless we are talking about biological fathers: Father--<Child )
politician ---- nose
Cat >---< Owner (even though an Owner may own more than one cat, a cat might have more than one owner)
car --- driver (a car can only be driven by one driver, a driver can only drive one car at one time)
wheel --- unicycle
House >--- Postcode (a house is assign one postcode, but that same postcode might be assigned to many houses)