Download
dbs201 relational databases n.
Skip this Video
Loading SlideShow in 5 Seconds..
DBS201: Relational Databases PowerPoint Presentation
Download Presentation
DBS201: Relational Databases

DBS201: Relational Databases

199 Views Download Presentation
Download Presentation

DBS201: Relational Databases

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. DBS201: Relational Databases

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

  3. 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

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

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

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

  7. 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

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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. Discovering Attributes

  16. 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

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

  18. Discovering Attributes

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

  20. 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

  21. 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

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

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

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

  25. 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

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

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

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

  29. 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