DBS201: Relational Databases

DBS201: Relational Databases

Agenda • Entity Relationship Model • Discovering Attributes • Identifying Keys • Defining Relationships • Relational Model

Entity Relationship Diagram • Shows entities – similar to a table structure • An entity is an object that exists and is distinguishable from other objects • An entity is represented by a set of attributes • An attribute has a domain (i.e. a set of permitted values) • Entities are associated by relationships

Attributes • Attributes are characteristics of Entities. • They describe an entity • Different types of attributes: • Simple • Composite • Single-Valued • Multi-Valued • Attributes have a Domain

Attributes • Simple • An attribute that can not be divided • i.e. FIRST_NAME

Attributes • Composite • Attributes that can be further subdivided to yield additional attributes • i.e. NAME can be subdivided into FIRST_NAME, INITIAL, and LAST_NAME

Attributes • Single-Valued • An attribute that can have only a single value • Not necessarily a simple attribute • i.e. Customer Number, Part Number

Attributes • Multi-Valued • An attribute that can have many values • i.e. Education • Two possible ways to deal with these: • Create attributes for each value • Create a new entity composed of the original multi-valued attributes components • Second approach is preferred

Discovering Attributes • Remember requirements gathering approaches? • Different approaches: bottom-up/top-down • Top-down uses an overview/narrative of a system to discover entities • Bottom-up uses screen/report samples to discover attributes, then these are grouped into entities

Discovering Attributes • Top-down • Interview client • Look for description of entities (i.e. their characteristics in a narrative) • Bottom-up • Use fields on screen/report samples

Discovering Attributes • From this example: • A composer writes/creates many compositions. • The composer includes the name, country, year of birth, year of death and era (e.g. classical, baroque, romantic, or modern). • The composition includes a title, type of composition (symphony, concerto, instrumental, chamber, opera, or choral) attributes attributes Attributes – because these describe the entity

Discovering Attributes • The composer includes the name, country, year of birth, year of death and era (e.g. classical, baroque, romantic, or modern). • The composition includes a title, type of composition (symphony, concerto, instrumental, chamber, opera, or choral) No reference to era; assuming it is another entity No to composition type; assuming it is another entity

Discovering Attributes • Potential Attributes: • Team • Title • Player • Name, Address, DateOfBirth, Phone Number • Sponsor • Name, Address, Phone Number, Fax • Coaches • Name, Address, Phone Number • District • Name, Description

Discovering Attributes

Discovering Attributes • From this example: Multiple Real Estate Listing Service (MRELS) is a company that has offices throughout Southern Ontario. These offices have a number of Real Estate agents working for them. MRELS deals in both residential and commercial real estate. Agents work with customers to get a listing or to find a listing for a customer. A listing will include the address and the type of property (residential or commercial), square footage, annual taxes and closing date. If it is a residential property, it will include number of bedrooms, number of bathrooms, special features (central air, heated by gas or oil, radiators or forced air, for instance). For a commercial property, it will include number of offices as well as number of docking bays (where trucks back up to be loaded/unloaded). Some attributes described – We will have to assume what some of the attributes might be

Discovering Attributes Potential multi-valued attribute; therefore create a new entity

Discovering Attributes

Identifying Keys • A key is used to identify and locate the information contained in a particular record • A key consists of one or more attributes that determine other attributes • A multi-attribute key is called a composite key • Two types of keys we will address: Primary Key and Foreign Key Identified from original list of attributes Identified through relationships

Identifying Keys • Primary Key • Uniquely identifies any given entity • If an entity does not contain an attribute that would be a good Primary Key candidate, then need to create a new attribute to be the Primary Key Course code could uniquely identify this entity and therefore is a candidate to be a primary key None of these attributes could uniquely identify a composer; therefore, need to create a new entity

Identifying Keys • What should be the Primary Key for the following examples?

Identifying Keys • Foreign Key • Created automatically through relationships between entities • An attribute whose value matches the primary key value in the related table

Defining Relationships • 1:1 • Determine most obvious path (i.e. which entity is the true ‘1’). Take the PK from that true ‘1’ and make it an FK in the other ‘1’ • 1:M • Take the PK from the 1 and make it an FK in the M • M:N • Create a bridge table and take the PKs from each original table and make the PKs and FKs of the bridge table

Relational Model • Entities translate into tables • Attributes translate into fields • Table is made up of columns (fields) and rows (records)

Relational Model Primary keys are obvious – they uniquely identify a row. Foreign keys are obvious – they refer to a field in another table.

Database name: Ch03_TransCo Table name: TRUCK Table name: BASE Table name: TYPE Relational Model • FIGURE P3.23 • What are the Primary Keys for each table? • What are the Foreign Keys for each table? • Draw the ERD

DBS201: Relational Databases