44220: Database Design & Implementation Physical Data Modelling

1. 44220: Database Design & ImplementationPhysical Data Modelling Ian Perry Room: C49 Tel Ext.: 7287 E-mail: I.P.Perry@hull.ac.uk http://itsy.co.uk/ac/0506/sem2/44220_DDI/

2. The ‘Data Modelling Stack’

3. What is a Physical Data Model? • The Physical Implementation (hardware & software) of a Logical Data Model: • it is useless to progress to this stage of database development if your Logical Data Model is NOT demonstrably ‘robust’. • A Physical Data Model must enable: • data to be stored (& maintained) in structured manner; i.e. MUST be an ‘accurate’ implementation of the logical data model. • retrieval of specific groupings of data; i.e. in order to provide the information REQUIRED by the original business requirements. • There may be several software constraints: • depending upon the software application chosen.

4. Our Physical ‘World’? • RDBMS Software: • Microsoft Access • In this physical world we MUST be able to: • Create; • i.e. translate our Relational Schema into a Database. • Populate; • this Database with ‘test’ data. • Query; • i.e. ask questions of the Database.

5. Properties of RDBMS Software • Modification: • To Schema: • creating & deleting relations (i.e. Tables). • adding attributes to, or removing attributes from, existing relations. • To Data: • creating & deleting tuples (i.e. Records) • updating attribute values (i.e. Data in Fields) • Interrogation: • Relational Algebra – how it SHOULD work. • Relational Calculus – how it DOES work.

6. Logical => Physical • i.e. translate our Relational Schema into a Database Storage Model: • Schema => Database • Relations => Tables • Attributes => Field Names • Domains => Data Type Field Size Input Mask Validation Rule etc. • Key Fields => Relationships

7. The SSC Database ER Diagram N M 1 M Course Staff Student Relations • Staff (StaffID, FirstName, SurName, ScalePoint, DOB) • Student (EnrolNo, FirstName, SurName, OLevelPoints, Tutor) • Course (CourseCode, Name, Duration) • Team (CourseCode, StaffID) • Pay (ScalePoint, RateOfPay)

8. Physical Implementation

9. Relational Algebra • With most (all?) Relational DataBase Management Systems the means of database interrogation is based upon: • Relational Algebra (E. F. Codd, 1972) • As represented by the 3 primary functions of: • SELECT • PROJECT • JOIN

10. Employee (EmpNo, EName, ESalary, Dept) Relational Algebra - Example M 1 1 M Employee Job Assignment ER Diagram Relations Assignment (JName, EmpNo, Hours) Job (JName, Budget)

11. SELECT • Extracts TUPLES (Rows) from a relation subject to required conditions on attributes in that relation. e.g.: SELECT Employee WHERE ESalary > 13000

12. PROJECT • Extracts COLUMNS from a relation in a named order by attribute. e.g.: PROJECT Employee OVER EName, Dept

13. JOIN • COMBINES RELATIONS which have a common attribute to generate a temporary relation containing all of the attributes from both relations. e.g: JOIN Employee AND Assignment OVER EmpNo NB. This temporary relation contains only one instance of the common ‘EmpNo’ Attribute.

14. Asking ‘Complex’ Questions • What are the Names, Jobs and Hours worked by those in the Sales Dept? PROJECT ( SELECT ( JOIN Employee AND Assignment OVER EmpNo) WHERE Dept = 'Sales') OVER EName, JName, Hours NB. The final relation is constructed by working from the innermost nesting outwards.

15. Structured Query Language • SQL is the most often used method for accessing relational databases. • A ‘software interpretation’ of Codd's Relational Algebra. • Remember: • SELECT - extracts TUPLES from a relation subject to required conditions on attributes in the relation. • PROJECT - extracts COLUMNS from a relation in a named order by attribute. • JOIN - COMBINES RELATIONS which have a common attribute to generate a temporary relation containing all of the attributes from both relations. • SQL ‘works’ for all RDBMS applications: • e.g. Access, Oracle, etc.

16. The SQL ‘SELECT’ Statement • SQL Syntax SELECT {column_name [, column_name, ... ] } FROM table_name [ table_alias ] [ WHERE condition [ AND/OR condition [, AND/OR condition, ... ] ] ] [ GOUP BY column_name [, column_name, ... ] [ HAVING condition ] ] [ ORDER BY {column_name/column_number [, ... ] } ] • Don’t worry, it is not a frightening as it looks!

17. The ‘simplest’ SELECT SELECT * FROM staff ; • A SELECT of all Tuples (rows) from a Table called staff.

18. A ‘more useful’ SELECT SELECT name, surname, age, position FROM staff WHERE age > 35 OR position = 'CLERK' GROUP by age ; • A PROJECT of specific columns of the staff Table (in a named order), with some SELECTion of conditions.

19. Previous SQL Statement Produces:

20. SELECTing from 2 Tables SELECT S.name, S.surname, S.age, C.courseno FROM staff S, course C WHERE S.surname = C.surname AND age > 50 ; • JOINs the Tables staff and course to create a temporary table and PROJECTs columns from this new table based on the SELECTion criteria.

21. Previous SQL Statement Produces:

22. Query-By-Example (QBE) • SQL can be difficult to learn, however, most RDBMS software has a QBE interface: • which presents the user with ‘lists’ of things to choose from. • Using this 'point-&-click' QBE interface, we don’t have to know (much) about: • Field Names, Logical Operators, etc. • can easily set up relationships between tables: • Staff.SURNAME = Course.SURNAME • and apply criteria for selection: • Staff.AGE > 50

23. This Week’s Workshop • Provides a ‘gentle’ Introduction to Microsoft Access • Showing you how to build, and then ask questions of, relatively simple Relational Databases. • i.e.: • Databases consisting of two, or three, Tables.