key definitions and concepts

For an Entity-Relationship diagram (ERD):

• An ERD is a design tool that aims to show all of the entities, attributes and relationships of a solution in one clear graphic representation.
• An entity is enclosed in a rectangle
• An attribute is enclosed in an oval
• A primary key can be identified by either changing the colour of the attribute's oval, or underlining the attribute name
• A relationship is enclosed in a diamond and is represented by a verb (or two words that describe an interaction)

For a Data Structure Diagram (DSD):

• A DSD is a common layout approach for displaying database relationships from within a RDBMS (such as Microsoft Access).
• Each entity represents a specific table and is therefore the heading listed.
• Each attribute represents a specific field and is shown as a new line beneath the heading.
• A primary key can be represented by adding an underline to one attribute.
• Each relationship is created as a line between two tables, from the a primary to foreign key.
• Crows feet notation can be used in order to display the cardinality of the relationship (e.g. 1:1, 1:N, M:N)

KEY SKILL TO DEMONSTRATE KNOWLEDGE (#3)

You need to know how to create an Entity-Relationship diagram (by hand) to display in a visual form how database tables and fields relate.

From the previous example, we already know the following information:

• Entities: Customers, Pizzas, Orders
• Attributes: Name, Address, Postcode, Phone number, email (Customers); Type, Prices (Pizzas); Customer, Type ordered, No. of pizzas ordered, time ordered, date ordered (Orders)
• Relationships: One Customer can place Many Orders (1:N); Many Orders can contain Many Pizzas (M:N)

From this information, we can draw an Entity-Relationship Diagram:

Pizzas on the Go Entity Relationship Diagram

Key skill to demonstrate knowledge (#4)

You need to know how to create a first draft of a Data Structure Diagram (by hand) to display in a visual form commonly used in RDBMS how database fields and tables inter-relate.

To create a first draft data structure diagram, you need to create a list of tables, fields and relationships that will translate into a RDBMS. This means that many to many relationships may need to be updated to two one to many relationships with a linking table containing two foreign keys. It also means primary keys need to be formally defined.

We are going to add an OrderID and CustomerID field to be a more reliable primary key for the Orders and Customers tables. Pizza type can be considered unique and can be linked to the Type in the Orders table as a foreign key.

Crows feet notation is used for the relationships between tables, with a straight line used for "one" and a three pointed line used for "many".

Pizzas on the Go - First draft of a DSD

To complete the data structure diagram, you will need to go through the process of database table normalisation and complete a data dictionary before finalising the table names and relationships.