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 -> many employees • One employee -> 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 ∞ (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: >=0 Salary: >=0 MinimumWage >= 7.5 Budget: >= 100,000 and <= 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/compsci181/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 <attribute names> from <tables> where <condition> • 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 • ProjectCustomeronlastName, firstName with duplicates • select lastName, firstName from Customer • ProjectCustomeronlastName, 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 orderby 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 hoursWorkedfrom 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 numRentalsfrom PreviousRental groupby videoId

30. Update statements • General form of update statement • update <table> set <attribute>=<value> ...where <selection condition>

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