ESM 261 - Fall 2008

Deliverable: Logical Schema

What you have to do

Develop a logical schema (structural model) for the relational database that will support your dataset. This means you have to identify and describe the

that the database will implement.

Use the following textual notation. First, for each entity you've identified, prepare a list of its attributes:

entity name
attribute type domain required?
attribute name

attribute type, one of:

  • text
  • integer
  • real number
  • date/time
  • Boolean
  • URL

further restrictions on type;e.g.:

  • list of legal text values
  • range of legal numbers or date/times
  • etc.

indicate whether the attribute must always have a value
attribute name ...    
...      

Then prepare a single list of relationships:

relationships
entity relationship entity
entity name

one of:

  • 1:0..1
  • 1:1
  • 1:0..n
  • 1:n
  • n:0..n
  • n:n

entity name
entity name ...  
...    

For example:

Student
attribute type domain required?
last name

text

free text

required
first name text free text required
shoe size text free text, or
enumeration of legal shoe sizes		 optional

Course
attribute type domain required?
course

text

free text, or
enumeration of legal course codes

required
room text free text, or
enumeration of legal room names		 required

relationships
entity relationship entity
Student

n:n

Course

Note that at the logical level you shouldn't be worrying about relationships between keys, only relationships between entities. When you get to the physical design level, you'll worry about how the relationships you've identified will actually be implemented.

Deliverables

Required: A document (Word, HTML, text, ...) containing your logical schema.

Optional: A Visio drawing of your logical schema as a "database model".

Due: Fri 07 Nov