Database Beginnings

Scenario so far

• In our scenario we have people registering for training sessions. – The data about the training sessions was

placed in an XML file. – When users registered, a confirmation

message was displayed and sent through email.

– Not shown, but a simple extension, would be for some training administrator to be emailed as well, and a list of participants compiled.

Two lists to one database

• We would like to move from these two lists (training sessions and registrants for training) to one database.

• You can ask questions of a database like– How many people have had the Blackboard

Intro but not the Blackboard Intermediate?– How many computers are there in Olney 200?

And whom do I contact to reserve it? – Etc.

Entity-Relationship Modeling

• Entities are the main things about which you are collecting data. – E.g. people, subjects, training sessions,

locations, etc.

• Relationships are the connections between the entities– E.g. people attend a training session, a

training session covers a subject, a training session is at a location, etc.

Normalization

• In normalization you start by looking at some existing data (or some anticipated data) output, such as our list of training sessions.

• The main concept behind normalization is to minimize data entry and data updating.

XML data for training

Note how the title and description of “Blackboard Introduction” is repeated. Normalization says to separate them off.

Note how the location Olney 127 is repeated. Normalization suggests that might get separated out – especially if we include additional information about location.

Separate into what?

• In ER modeling, the entities and certain types of relationships end up represented by “tables”.– Tables look like that XML Data Grid

• Normalization also leads one to a decision on how the tables should be organized.

• ER Modeling and Normalization should lead one to the same end point – a set of tables with connections to one another with a minimum of repeated data – a database.

Table?

• A table can be seen as the Data Grid seen earlier. – It has rows corresponding to different

“records”– The columns correspond to different fields or

properties of a record– Each row/record should be unique– The order of rows does not matter– The order of the columns does not matter

Connected how?

• Special columns/fields called keys play a special role of establishing the connection/relationship between records in different tables.

• For example, a Location record might have a code O127 (a primary key) to identify it, and a TrainingSession record may have a location value O127 (a foreign key) to indicate its relationship to (that it will be held in) the location corresponding to O127.

List Entities and Attributes

• A Person has – A username (can be primary key?)– A first name– A last name– A phone number – An email address – Etc.

List Entities and Attributes (Cont.)

• A Location has – A code to use as primary key– A name– A number of seats– A contact person – Etc.

List Entities and Attributes (Cont.)

• A TrainingSubjectMatter has – A code to use as primary key– A name– A description– A level? – Etc.

List Entities and Attributes (Cont.)

• A TrainingSession has – A code to use as primary key– A subject (relates to TrainingSubjectMatter)– A location (relates to Location)– A date– A time– Etc.

• Some might see this as an entity, some might see it as a relationship, either way it is a table

Relationships

• That a TrainingSession had a location is easy to handle because a TrainingSession has a single location. The relationship is established by using the Location’s id (primary key) as a property of a TrainingSession.

• But what about the relationship that a TrainingSession has attendees? Such a relationship is said to be many-to-many– A session has many people attending– A person may attend many sessions

Many-to-many means separate table

• You can’t make session a property of a Person, he or she may attend many sessions.

• You can’t make person a property of a Session, many people will attend a session.

• You make a PersonAttendsSession table– It has a foreign key to indicate the Person– It has a foreign key to indicate the Session