Relational Database

1
Relational Database
2
Lesson Plan

• Main concepts
• Practice

3
Relationships

• One to one relationship
• each record is related to only one record in the
  related table
• Example
• 1 employee ? 1 soc_sec
• 1 soc_sec ? 1 employee
• 1 book ? 1 ISBN
• 1 ISBN ? 1 book
• Note that the relationship is one to one in both
  directions.
• - Entities with a pure one to one relationship
  usually belong together in one table

Question is child-mother a one-to-one
relationship?
4
Relationships

• One to many relationship
• each record in a table can have multiple related
  records in another.
• Example
• One job -gt many employees
• One employee -gt one job
• A one to many relationship is always one to many
  in one direction and one to one in the opposite
  direction.

5
Relationship

• One to many relationship (cont.)
• The table with the Foreign key is the child while
  the table with the list of possible values
  (usually the Primary Key) is called the parent
  table.
• Entities with a one to many relationship usually
  belong together in two tables

6
Relationship
Is student-course one to many relationship?
7
Relationship

• Many to many relationship
• each record in a table can be related to
  multiple records in another table and vice versa.
• Example 1 student ? many courses and 1 course ?
  many students
• Entities with a many to many relationship usually
  belong together in three tables
• the relationship is modeled as two of one to many
  relationships and is involving an intermediary.

8
Relationships
9
Viewing Relationship Types

• The Relationship tool shows characteristics of
  relationship types and roles
• To-one relationship role has a 1
• To-many relationship role has a 8 (infinity
  symbol)

10
Entity-Relationship Model

• An E-R model is a data model that represents
  information at conceptual level and includes
• Entity classes (e.g represented by nouns)
• Attributes of each class (e.g represented by
  adjectives)
• Relationship types between classes (e.g
  represented by verbs)
• Constraints
• Types of attributes
• Designation of key attributes
• Cardinalities of relationship types
• Developed by Peter Chen (at MIT)

11
Entity-Relationship Model

• An E-R Model is typically represented graphically
• E-R diagram, the technique we use
• UML diagram, an emerging standard for specifying
  E-R models and software design

12
Entity Relationship Diagrams
13
Practice

• Relationship identification with multiple choice
  and fill-in-blank questions

14
Table design rules

• Normalization the process of transforming some
  objects into a structural form that satisfies
  some collection of rules
• First Normal Form a table describes a single
  entity class, no repeating fields. Every record
  has the same number of fields. Single field can
  not contain multiple data values

15
Table design rules

• Second Normal Form
• Requires data in all non-key fields be fully
  dependent on the value of a composite primary
  key.
• Third normal form
• Requires data in all non-key fields of the table
  be fully dependent on the value of the primary
  key and completely describe the object.

16
Table design rules

• Fourth Normal Form
• Requires splitting tables that consists of lists
  of independent attributes
• Fifth normal form
• Requires the capability to reconstruct the
  source data from the tables that have redundant
  data removed.

17
Validating data

• Field-level validation rules
• Restrict the values entered in a field
• For example
• Grade can be A,B,C,D,F
• Points gt0
• Salary gt0
• MinimumWage gt 7.5
• Budget gt 100,000 and lt 500,000
• Demonstration in MS Access

18
Validating data

• Table-level validation rules
• Validation rules in which the value of one field
  depends on a previously entered value in another
  field of the current record
• Demonstration in MS. Access

19
Validating data

• Simple lookup list to each field
• Demonstration in MS Access

20
Practice

• Download Example database on the web
• http//facstaff.uww.edu/nguyenh/uww_courses/comps
  ci181/lectures/example.mdb
• Implement lookup list for Description field, and
  constraints for Budget fields for Department
  table
• Description can only be marketing,accounting,
  finance,customer_service
• Budget is in the range 100,000-500,000

21
Composite and single attributes

• composite attribute
• Address (which contains street, town and zip
  code)
• Single attribute
• Zipcode
• Street
• Town

22
Metadata

• Meta data
• is data that describes the structure of data
• Information about data
• Description of the content, quality, condition,
  and other characteristics of data. Metadata help
  a person to locate and understand data

23
How to create queries in SQL

• SQL is a language with
• Syntax the form of statements
• Semantics the meaning of statements

24
Simple select statements

• Select statement in SQL combines many relational
  operations
• select ltattribute namesgt from lttablesgt where
  ltconditiongt
• select clause
• specifies the attributes that go in the results
  table.
• from clause
• specifies the source tables that the database
  will access in order to execute the query.
• where clause
• specifies the selection conditions, including the
  join condition
• Demonstrate in Access

25
Examples of Simple Select Statements

• Examples
• Project Customer on lastName, firstName with
  duplicates
• select lastName, firstName from Customer
• Project Customer on lastName, firstName without
  duplicates
• select distinct lastName, firstName from Customer
• select from Customer where lastName Doe
• select from Customer where lastName 'Doe'
• Notice the use of string literals with single
  quotes
• Doe

26
String pattern matching and ordering results

• Pattern matching in where clause
• select from Movie where title like
  'alligator'
• select from Employee where ssn like
  '___-44-____
• Ordering results in SQL
• select from Movie where title not like 'The '
• select from Customer order by lastName,
  firstName
• Demonstrate in MS Access

27
String pattern matching and ordering results

• Ordering is part of SQL
• Relational model declares table rows are
  unordered
• SQL and Access treat tables as lists of rows in
  some order
• Unordered queries return a list of rows in no
  particular order
• The server can produce rows in any order
• Generally produces rows in the order that is
  easiest or fastest to create

28
Expressions, literals, and aggregates

• Expressions and literals in select clause
• select lastName, firstName, Employee.ssn, date,
  (endTime-startTime)24 as hoursWorked from
  Employee, TimeCard
• where Employee.ssn TimeCard.ssn
• Aggregates putting many input rows into 1 output
  row
• select count() from Rental where accountId 101
  
• select count(distinct lastName) from Customer
• Demonstrate in MS Access

29
Aggregating with group by

• Group by is used to
• divide input rows into groups and
• Produce one output row per group
• select videoId, avg(cost) as averageCost,
  sum(cost) as totalCost, count() as
  numRentals from PreviousRental group by videoId

30
Update statements

• General form of update statement
• update lttablegt set ltattributegtltvaluegt ... where
  ltselection conditiongt

31
Delete statements

• Each delete statement deletes from one table
  according to some selection clause
• Delete every row from table
• delete from Employee
• DEMONSTRATE in Access

32
Practice

• Compute the results of SELECT statement
• Perform SQL statement