1 / 29

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

nirav
Télécharger la présentation

DBS201: Relational Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related