CSE1IS Information Systems Week 7 Lecture 2 Entity-Relationship Diagram Example

1. Relational Database:

• Database is made up of many tables each of a fixed length record structure ( A table may be stored as a single file - that is up to the DBMS )
• There is no physical connection between tables, however they are linked by cross-references.
• Each table has columns (also called attributes or fields), coulmn headings and rows (records or tuples)
• Each column is of a fixed data type eg. numeric (integer, currency, decimal..), character, etc.
• The overall picture of the complete database is called its 'schema', often displayed as an ERD.
  A 'subschema' is just a part of the database.

This Packing Slip - and others - could be stored in a relational database (abbreviated) as:

Table 1: Inventory

Table 2: PackingSlip

PSLIP#	CUST#	SHIPDATE	SHIPTO	INSTRUCTIONS
1286950	547	22-08-2006	Sally's ...	Before noon
1286952	33465	23-08-2006	Harry's ...	Contact Fred ...
:

Table 3: PackingItems

PSLIP#	ITEMCODE	QTY ORDERED	QTY SHIPPED	FILLED
1286950	54602	6	4	N
1286950	30011	3	3	Y
1286952	23764	5	5	Y
1286952	46587	3	2	N
1286952	54602	10	10	Y
:

Can you construct an ERD for the Packing Slip?

2. Converting E-R Diagrams into Relational Tables:

Three steps:

1 Entities:

• Each entity becomes a table
• Columns of the table are the attributes of the entity
• Primary key for the table is the same as the primary key of the entity

2 One-to-Many Relationships:

• The Primary Key of the One is added as a column in the Many sided entity of the relationship
• This column(s) is a Foreign Key of the entity.

3 Many-to-Many Relationships:

• Create a new table for the relationship
• Insert the primary keys of both entities in the relationship into the new table
• The Primary Key of the new table is the concatatenation of these keys
• Add any other attributes that belong to the relationship.

eg.:

becomes:

  
3. Student-Course Example from text:

From S.C.&R. pp.329-333:

'TAKES' is M:N relationship (Associative Entity) and so may be converted into an entity:

And, the tables then may look like:

4. Example based on the lecturer's subject administration system

In particular the extra tute question Week 6 Tute 2 (with solution )

Step 1: define Entities:

Step 2: define Relationships:

Step 3: draw ER Diagram:

Step 4: define Relational Database tables:

References:

• Shelly, Cashman & Rosenblatt, Systems Analysis and Design, 6th Edition,  Course Technology, 2006.

NOTE: this webpage has not been brought up to WCAG 1.0 standard nor does it contain validated HTML