Logic - University of Calgary

Logic - University of Calgary

1 Databases & Data Modeling Peeking into Computer Science Jalal Kawash 2010 Mandatory: Chapter 4 Sections 4.1 to 4.3 Reading Assignment Peeking into Computer Science Jalal Kawash 2010 2 Databases 3 At the end of this section, you will be able to: 1.

2. 3. Describe what a database is Draw the relationship between databases and mathematical relations Describe what a database schema is Objectives Peeking into Computer Science Jalal Kawash 2010 To store & retrieve information Sale $$$ Sale $$$ JTs Extra: Purpose Database:

Peeking into Computer Science Customer Jalal Kawash 2010 information Why bother, why not use a simple file as an alternative? E.g., tracking client information MILES EDWARD OBRIAN DS9 Corp Electrical engineering 2007 purchases: $10,0000,000 2006 purchases: $1,750,000 JAMIE SMYTHE Cooperative services Gasoline refining 2006 purchases: $5,000,0000 2005 purchases: $5,000,0000 2004 purchases: $5,000,0000 2003 purchases: $5,000,0000 2002 purchases: $5,000,0000

SCOTT BRUCE Bryce Consulting Investment analysis 2007 purchases: $500,000 2006 purchases: $1,500,000 2005 purchases: $2,500,000 2004 purchases: $500,000 If the list is short then a simple text file may suffice. As the list grows organizing and updating the information becomes more challenging (duplicates or inaccuracies?) Validity must be manually checked. Also searching the list according to specific criteria may become difficult . e.g., Show all clients whose purchases in 2007 were between one and five million dollars e.g., Show all clients that made in one year a purchase exceeding 10 million dollars. ETC. JTs Extra: Why Bother?

Peeking into Computer Science Jalal Kawash 2010 Organized collection of data Minimizes redundancy: Wastes space and produce anomalies Makes it easier to access and modify data Examples: Typically University and bank records is a collection of tables Database Peeking into Computer Science

Jalal Kawash 2010 7 Example Database Peeking into Computer Science Jalal Kawash 2010 8 Information database): is commonly stored in tables (relational Employees table JTs Extra: Storing Information In A Database Peeking into Computer Science Jalal Kawash 2010

Record: An example instance (row) of data within the table. Records of the table (rows) One record, Simpson, Homer JTs Extra: Storing Information In A Database (2) Peeking into Computer Science Jalal Kawash 2010 Field: are attributes used to describe each record in a table Fields of the table (columns) Address

field describes location JTs Extra: Storing Information In A Database (3) Peeking into Computer Science Jalal Kawash 2010 1. 2. 3. 4. 5. 6. Create a unique and descriptive name. Do not use words that convey physical characteristics or database terminology. While names should be short avoid using acronyms and abbreviations unless they

are well-known. Do not use proper names or words that will restrict the type of data to be entered into the table. Consider using the plural form of a name. Avoid the use of spaces in names. JTs Extra: Guidelines For Naming Tables Peeking into Computer Science Jalal Kawash 2010 1. 2. 3. 4. 5. Create a unique and descriptive name. Create a name that accurately, clearly and unambiguously identifies the

characteristic that the field represents. While names should be short avoid using acronyms and abbreviations unless they are well-known. Use the singular form of a name. Avoid the use of spaces in names. JTs Extra: Guidelines For Naming Fields Peeking into Computer Science Jalal Kawash 2010 Relations Peeking into Computer Science Jalal Kawash 2010 14 Let: N be the set of natural numbers M be the set of names

L be the set of locations Then: PROJECT N x M x L Relations Peeking into Computer Science Jalal Kawash 2010 15 JTs Extra (database schema) Tables? Fields? Relationships? Database Schema Peeking into Computer Science Jalal Kawash 2010

16 SIN Last name Given names Sales 111111111 Tam James $1 SIN Last

name Given names Position 222222222 Gtrezky Wayne Center JTs Extra: Example Database Schema Peeking into Computer Science Jalal Kawash 2010 Data Modeling Entity-Relationship Model

18 At the end of this section, you will be able to: 1. Understand ER the model 2. 3. 4. Understand and differentiate between entity types, entities, relationship types, and relationships Understand attributes and primary keys Understand relationship type cardinality Understand universal and existential participation in relationships Use ER diagrams to design data models Objectives

Peeking into Computer Science Jalal Kawash 2010 Entity: an object that exists in the real world Physically: book, car, student Conceptually: job, route Entity-type: a class of entities Employee Project Department Entities Peeking into Computer Science Jalal Kawash 2010

20 Entities have attributes Properties that describe entities An Employee can be described by: SIN Name DOB Gender Address Attributes Peeking into Computer Science Jalal Kawash 2010

21 They are used to graphically represent a database. An ERD shows: Tables, Fields of a table Relationships between tables (more on this later). JTs Extra: ERDs (Entity-relation diagrams) Peeking into Computer Science Jalal Kawash 2010 EMPLOYEE - SIN - First name - Last name - DOB

- Gender - Salary - Number - Street - City - Postal Code PROJECT - Number Name Location DEPARTMENT - Number - Name Entity Types in ER Diagrams Peeking into Computer Science Jalal Kawash 2010

23 Each table should typically have one field designated as the primary key: The primary key must be unique (identifies one record from another). Primary Key for table Employees is the SIN field JTs Extra: Primary Key Peeking into Computer Science Jalal Kawash 2010 Primary Key: a collection of attributes the

uniquely identify an entity One attribute most of the time SIN for employee Student ID Underlined in ERD Primary Keys Peeking into Computer Science Jalal Kawash 2010 25 A primary key must be unique to each record because it is the one thing that distinguishes them. If there is at least (or even exactly) one instance

(however unlikely) where records can take on the same value for a field then that field cannot be a primary key. (When in doubt if this will ever be the case then verify with your users). If a single key field cannot be found then several fields can be combined into a composite key. (Each field is still a separate field but together they form a unique primary key for each record). JTs Extra: Choosing A Primary Key Peeking into Computer Science Jalal Kawash 2010 If a unique primary key still cannot be found then invent one. JTs Extra: Choosing A Primary Key (2) Peeking into Computer Science

Jalal Kawash 2010 One-to-one One-to-many (many-to-one) Many-to-many Cardinality (JT: Multiplicity) of Relationships Peeking into Computer Science Jalal Kawash 2010 28 1. One to one relationships One entity participates in the relationship from the left and one entity participates in the relationship from the right.

Person : head Worker : Social Insurance Number This type of relationship is rare in databases 2. One to many relationships On one side of the relationship one entity participates in the relationship while on the other side: zero or more entities may participate in the relationship. Person : Hair Department : Employee JTs Extra: Cardinality Peeking into Computer Science Jalal Kawash 2010 3.

Many to many relationships On each side of the relationship zero or more entities may participate in the relationship. Students : Classes JTs Extra: Cardinality (2) Peeking into Computer Science Jalal Kawash 2010 3. Many to many relationships This type of relationship is not directly implemented in databases: Students table StudentID StudentFirstName 123456 123457 123458 Jamie

Stacey Angel StudentLast Name Smyth Walls Lam StudentPhone 553-3992 790-3992 551-4993 Classes table ClassName CPSC CPSC CPSC ClassNumb er 203

231 233 Lecture No 01 01 01 ClassDescription Introduction to Problem Introduction to Computer.. Introduction to Computer.. JTs Extra: Cardinality (3) Jalal Kawash 2010 Peeking into Computer Science 3. Many to many relationships

Typically implemented as two one to many relationships in databases: Students table StudentID 123456 123457 Classes table StudentFirstNam e Jamie Stacey ClassName ClassNumber CPSC

203 CPSC 231 Registrations table (linking table) StudentID ClassName ClassNumber Lecture No 123450 ENGL 201 01

123457 CPSC 203 01 123460 MATH 271 01 JTs Extra: Cardinality (4) Peeking into Computer Science Jalal Kawash 2010

Represented using a solid line. Employee Department There are no employees who dont participate in the relationship between employees and departments There are no departments that dont participate in the relationship between employee and department JTs Extra: Full Participation (ERD

Representation) Peeking into Computer Science Jalal Kawash 2010 Represented using a dashed line. Employee Some employees that may not participate in the relationship between employees and projects There are no projects that dont participate in the relationship between employees and projects Project JTs Extra: Partial Participation (ERD Representation) Peeking into Computer Science Jalal Kawash 2010

EMPLOYEE - SIN - First name - Last name - DOB - Gender - Salary - Number - Street - City - Postal Code DEPARTMENT WORKS FOR - Number - Name CONTROLS PROJECT

WORKS ON Number - Name - Location - Relationships Types Peeking into Computer Science Jalal Kawash 2010 35 The degree of a relationship is the number of entity types it relates Controls is a binary relationship type

PROJECT Number - Name - Location - DEPARTMENT CONTROLS - Number - Name Relationship Degrees Peeking into Computer Science Jalal Kawash 2010 36 Entity types participate

Fully (universal participation) Partially (existential participation) in relationship types Participation Levels Peeking into Computer Science Jalal Kawash 2010 37 EMPLOYEE - SIN - First name - Last name - DOB - Gender - Salary - Number - Street - City - Postal Code

DEPARTMENT WORKS FOR - Number - Name CONTROLS PROJECT WORKS ON Number - Name - Location - Participation Levels Peeking into Computer Science Jalal Kawash 2010

38 ERD Examples 39 BOOK -ID -ISBN -Title -Author -Price -Publisher -Year SELLS TEXT Peeking into Computer Science Jalal Kawash 2009 SELLER

-ID -Name -email -Rating COURSE -ID -School -Department -Code -Section -Term -Year 40 SINGER -ID -Name -Bio -Website PERFORMS

Peeking into Computer Science RECORDS ALBUM -ID -Title -Release date SONG -ID -Title -Duration -Genre CONTAINS Jalal Kawash 2009 41 TREE

-Tree# - Year planted MEASUREMENT -Meas# -Branch# -Height -Trunk width -Month -Year ON OF SPECIES -Name -Bark color -Max height IN FOREST

-Fname -Area -Location -Altitude Peeking into Computer Science WORKS Jalal Kawash 2009 BY EMPLOYEE -Emp# -Name -Salary 42

Recently Viewed Presentations

  • Management Development

    Management Development

    Citibank, N.A. ATTN: RFCC GROUP, 11500 NW Ambassador Dr. 4th Floor, Kansas City, MO 64153. Or fax the documentation to: (877) 825-1726 Attn: RFCC (Recovery Fraud Claim Coordinator) Citi will complete its investigation within 60 days. Note: The Firm is...
  • OPTICAL MINERALOGY Geology 265 Mineraloji Meral Dogan Lecture

    OPTICAL MINERALOGY Geology 265 Mineraloji Meral Dogan Lecture

    The extinction angle is the angle between the length or cleavage of a mineral and the minerals vibration directions. The extinction angles when measured on several grains of the same mineral, in the same thin section, will be variable. The...
  • TALENT AND ABILITY - Amazon S3

    TALENT AND ABILITY - Amazon S3

    CREATIVITY AND MADNESS You don't have to be mad to be creative…but it may help. Anecdotally, there are strong links between bipolar disorder (esp. the manic phase) and artistic output in music, drama and painting. Some empirical support - e.g.,...
  • Welcome to Level 1 Safeguarding Children Presented by

    Welcome to Level 1 Safeguarding Children Presented by

    SKULL - fracture or bleeding under skull (from shaking ... her ribs were also broken and she had ulcers, burns and cuts to her leg. Jasmin's death led to the Children Act 1989. ... Her toes were struck with a...
  • Math Flash - PC|MAC

    Math Flash - PC|MAC

    Place Value What is the standard form of? What is the expanded form of? What is the expanded form of? What is the standard form of? What is the word form of? What are the periods of this number? How...
  • PASA PRIMATE WELFARE PASA Welfare Team What do

    PASA PRIMATE WELFARE PASA Welfare Team What do

    Freedom from pain, injury and disease - prevention or rapid treatment, (prevention, or rapid diagnosis and treatment, of injury and disease) ... Anthropomorphism by Omission - Unwittingly neglecting the animal's perspective and Umwelt (also a grievous sin) By Dr. Gordon...
  • Core Presentation

    Core Presentation

    Keeping safe with gas. Carbon monoxide. What is it? How will I recognise it? What are the symptoms? V4.2. What is it? Carbon monoxide (CO) is a highly poisonous substance produced by the incomplete burning of gas and Liquidfied Petroleum...
  • The Challenge: To Create More Value in All Negotiations

    The Challenge: To Create More Value in All Negotiations

    Many hospital administrators say they can't afford to take the necessary precautions." —Betsy McCaughey, founder of the Committee to Reduce Infection Deaths (New York Times/06.06.2005) "When I climb Mount Rainier I face less risk of death than I'll face on...