1 / 43

F28DM: DATABASE The Relational Model Physical Design

F28DM: DATABASE The Relational Model Physical Design. Topics. MySQL SQL DDL Bank Schema Create table statements Constraints Key, entity, foreign key – enforcing these Enterprise Alter and remove table Adding new tuples/rows. Physical Database Design.

haviva-ross
Download Presentation

F28DM: DATABASE The Relational Model Physical Design

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. Relational Model - Physical F28DM: DATABASEThe Relational ModelPhysical Design

  2. Relational Model - Physical Topics MySQL SQL DDL Bank Schema Create table statements Constraints Key, entity, foreign key – enforcing these Enterprise Alter and remove table Adding new tuples/rows

  3. creating tables Physical Database Design Physical database design is the process of producing a description of the database implementation on secondary storage. It is tailored to a specific DBMS system. It describes base relations (the tables where the data will be stored) and associated integrity constraints It also includes specifying (next lecture) file organizations indexes used to achieve efficient access to data

  4. Client server system • MySQL in the Linux lab is a client-server system. • There is ONE database on a server, and you connect to it from a client machine in the lab • using the mysql command at the command line • or the web tool phpMyAdmin. • Use either but you should understand the sql commands • ON Vision there is a user guide for using MySQL in the department

  5. creating tables SQL - Structured Query Language It was designed by IBM It is the standard relational language, and includes Data Definition Language, to define a schema Data Manipulation Language, to allow for the insertion, modification, retrieval, and deletion of data It has many dialects - but now has standards: SQL89 (SQL1) - first attempt at a general standard SQL92 (SQL2) – major revision, all DBMS should implement this SQL99 (SQL3) - extension to a full object oriented programming language SQL:2003 – more XML support SQL:2006 – even more XML support SQL:2008 – minor alterations SQL:2011 – minor alterations plus support for temporal databases (time-related data)

  6. creating tables DBMS and SQL Each DBMS conforms to a subset of the standards Each DBMS introduces their own additional features This doesn’t make it as easy as we might like to move from one DBMS to another

  7. creating tables SQL : Data Definition Language Data Definition Commands Create Commands - create a database object e.g. CREATE TABLE Alter Commands - allow database objects to be edited Drop Commands - delete database objects

  8. creating tables SQL : Data Manipulation language Data Manipulation Commands Insert - adds a new record to a table Delete - removes records from a table Update - changes the values in records in a table PLUS pto

  9. creating tables SQL – DML – later lectures Queries SELECT - retrieves specified attributes in table(s) depending on specified conditions Transaction Commands Commit – ends current transaction, making all changes permanent Rollback - undo all work in this transaction Security Grant - give a user or role access to a table or view Revoke - remove granted access

  10. creating tables Data Definition in SQL Creating Relations is achieved with the command CREATE TABLE – specify column names, domains and constraints It’s quite straightforward except for constraints, which are discussed in this lecture. You should always use the InnoDB engine, which is the only one which enforces foreign key constraints.

  11. Employee table For example (here domains and constraints are MySQL-specific): CREATE TABLE DBEmployee ( ssn INT(5) PRIMARY KEY , lastname VARCHAR(12) NOT NULL, firstname VARCHAR(12) NOT NULL, dateOfBirth DATE, gender ENUM ('M','F') NOT NULL, salary INT (11) NOT NULL, empdNum INT (11) NOT NULL, supssn INT (11), CONSTRAINT fk_empsup FOREIGN KEY (supssn) REFERENCES DBEmployee (ssn), CONSTRAINT fk_empdep FOREIGN KEY (empdNum) REFERENCES DBDepartment ) ENGINE=INNODB;

  12. creating tables Overview of Syntax SQL commands are not case sensitive, but a recommended approach is to have SQL words in uppercase Table names starting with a capital letter (like a java class) Column names starting with lowercase (like a java variable) In MySQL, it is essential to use the correct case for tablenames. If you can’t remember how you spelled it, use the command ‘SHOW TABLES’. However, typing sql commands is simpler if all characters are lower case!

  13. creating tables Overview of Syntax All SQL Commands are terminated by a semi-colon. In the CREATE TABLE command, use opening parenthesis after the table name. Each column definition is terminated with a comma, each table constraint is terminated with a comma, EXCEPT No comma before the closing parenthesis.

  14. creating tables Constraints in an Oracle Table Specification Column constraints determine the values allowed in one column, and can specify foreign key constraints They can be placed: with the column declaration, or at the end of the column declarations

  15. creating tables Constraints on Relational DB's Constraints limit the values which can be inserted into the database structure. Integrity Constraints form a set of rules which must hold for all relational DB's and are enforced by the DBMS Key constraints Entity constraints Foreign key constraints Enterprise Constraints are those rules which are specific to the particular application e.g. domain values

  16. creating tables Key Integrity Constraints Candidate key of a relation/table - any set of attributes of that table that uniquely identify that tuple/row in the table. One candidate key is chosen to be the primary key. e.g. For Department, the number and the name are both candidate keys. Choose the number to be the primary key. The key integrity constraint says that keys of a relation must be unique

  17. creating tables Key Integrity Constraints In MySQL: Primary keys in MySQL are automatically unique You should specify that other candidate keys must be unique staffNum VARCHAR2(6) PRIMARY KEY niNumber VARCHAR2(10) UNIQUE

  18. Relational Model - Physical Entity Integrity • The primary key of a table must be non – null. • (Because this is what is used to identify each row) • In MySQL – Primary keys are automatically non-null • Going back to the company database: • So, as long as we specify a primary key, the third line (Johnson with no ssn) wouldn’t be permitted   X

  19. Relational Model - Physical Foreign keys • A foreign key is an attribute or set of attributes which matches the primary key of some relation (possibly the same relation) • It’s the key of one table, which occurs also in another table • So Smith is in department 1, which is the HR department • empdNum is the foreign key which refers to dNum in the Department table

  20. Relational Model - Physical Referential Integrity (foreign keys) A referential integrity constraint is specified between two relations: Any attributes of a relation which are foreign keys to another relation must take values which either exist in the second relation (Smith in Dep 1) or are null (Brown not in a department). This assumes that null values are actually permitted in this field

  21. creating tables Referential Integrity (foreign keys) E.G. Table constraint empdNum INT(11) . . . . FOREIGN KEY empdNum REFERENCES Department (dNum)

  22. Relational Model - Physical Referential Integrity (foreign keys) What mustn’t be allowed: A foreign key in one relation which refers to a non-existent primary key in the other relation E.g. Smith works for a non-existent branch A ‘dangling pointer’ Queries like ‘ find the department name of the employee called Smith’ will fail ?

  23. InnoDB engine • ***In MySQL, • the only way to enforce foreign key constraints is to use the InnoDB engine, declaring this in the CREATE TABLE command. • Otherwise, foreign key constraints are ignored.***

  24. creating tables Enterprise Constraints In addition to the integrity constraints, it is useful to specify other constraints which ensure that the data held in the database remains sensible. check constraints are general constraints which limit the value (next slide) non-null constraints applied to non-primary-key fields (after next slide)

  25. creating tables Check Constraints Check constraints are general constraints which limit the value, to a set of values or a range these are included in Oracle and Microsoft Access. In MySQL, CHECK constraints are accepted (for compatibility) but ignored. There is an enum type in MySQL for a set of values e.g M or F. gender ENUM ('M', 'F')

  26. creating tables Non-null Specifying a field as a primary key ensures that it is not null Non-null constraints can be applied to non-primary-key fields e.g. Employee table – name must be supplied firstNames VARCHAR2(20) NOT NULL, You can specify total participation by constraining the foreign keys to be non-null. E.g. employees must be in a department empdNum INT(3) NOT NULL, FOREIGN KEY empdNum REFERENCES Department (dNum)

  27. creating tables Enforcing Integrity Constraints The DBMS must continually check that constraints are not violated every time an update occurs. Three options for how to proceed if there is a violation: allow the update anyway – essentially ignoring the constraint refuse to perform violating update compensate in some way.

  28. creating tables Enforcing Integrity Constraints When using a DBMS, always discover what it does! E.g. mySQL ignores foreign keys for many table types mySQL ignores CHECK constraints Etc

  29. Relational Model - Physical Integrity constraint options – in general

  30. creating tables Foreign key constraints examples In the Employee Table : supervisor VARCHAR(7) REFERENCES Employee (ssn) ON DELETE SET NULL When a row of Employee is deleted, the Employee supervisor columns which reference the deleted ssn are set to null In the Location Table : ldNum REFERENCES Department ON DELETE CASCADE When a row of Department is deleted, related rows in the Location table referencing that dNum are also deleted. There is no need to keep details of a department locations without the details of the related department. The default is NO ACTION – the alteration would be rejected.

  31. creating tables Oracle & Foreign key constraints There is no ON UPDATE CASCADE in Oracle, taking the view that Primary keys ought to be totally fixed If they are not, you could choose a different primary key such as a sequence number

  32. creating tables ALTER TABLE & FOREIGN KEYS Most foreign keys can be specified in the CREATE TABLE command – but only if the referenced table already exists. So, you need to ensure that you create tables that will be referenced, before you create the tables that reference them Sometimes 2 tables may reference each other (e.g. ‘works in’ and ‘manages’), and one of the foreign key constraints needs to be added later, using ALTER TABLE

  33. creating tables Altering a Table Specification Table definitions can be changed by the ALTER TABLE command. Columns can be added – each existing row gets a value NULL in the new field ALTER TABLE Employee ADD(title VARCHAR2(4)); Columns can be altered (replaced) – now salary can be null ALTER TABLE Employee MODIFY salary INT (11) default NULL;

  34. creating tables ALTER TABLE & FOREIGN KEYS For instance, in the Compan database, we could create the Department table first then the Employee table, including the foreign key in Employee table specifying which department the employee works in. But the foreign key in the Department table which specifies which employee is the department manager cannot be constrained to be a foreign key referencing the Employee table, until the Employee table has been created. ALTER TABLE Branch ADD (FOREIGN KEY (manager) REFERENCES Employee(staffNo) );

  35. More on altering • Some people add all foreign keys later. ALTER TABLE DBDepartment ADD FOREIGN KEY (manager) REFERENCES DBEmployee(ssn) ); • If you are deleting and recreating a database, it is a lot simpler to temporarily suspend foreign key checks (see DBCompany.sql).

  36. creating tables Removing Tables A table can be removed with the command DROP VERY DANGEROUS! All data in the tables is deleted and the table is removed. Useful if you decide to rebuild your database DROP TABLE DBProject; Or DROP TABLE DBProject CASCADE CONSTRAINTS;

  37. creating tables Data Manipulation in SQL Adding New Tuples/Rows There are 2 ways of doing this inserting new values one tuple at a time bulk loading

  38. creating tables 1. Inserting all the values (don’t omit any): INSERT INTO DBEmployee VALUES (333, ‘Richard’, ‘Brown’, 1, null); 2. Inserting some specified values: INSERT INTO DBProject (pName, pdNum) VALUES (1'Turing', 2); If columns are omitted, the column default will be assigned. INSERT statement

  39. creating tables Bulk Loading and Dumping of Data Clearly, inserting values one tuple at a time is slow, so there is a mechanism for loading many tuples at a time - this is called bulk loading In Oracle the SQLLoader loads data from text files into empty tables There are Import and Export utilities for backing up data and moving it between Oracle databases Not useful in the coursework, the data is in Oracle format

  40. creating tables Checking contents of a table Use the SQL SELECT command (more about this in the next lecture) to list all the data in the table Include the semi-colon SELECT * FROM tablename; More about SQL next time...

  41. creating tables Answers - preferred action on updates An employee leaves the company and his details are deleted (assume we don’t want to keep his details for historical reasons). What should happen to details about his dependents Delete them too, no longer interested in them the details of the hours of the projects he worked on Delete them too, no longer interested in themOR, we do want to keep this, so we shouldn’t delete the employee at this stage the employees that he supervised They must not be deleted! But they will need an alternative supervisor. Set this null or alter the supervisor before deleting the employee the department that he managed This must not be deleted! As above, will need an alternative manager

  42. Relational Model - Physical Answers - preferred action on updates We discover that an employee has the wrong staff number. What should happen to all the foreign keys in related tables? It would be nice to cascade this update into all the related tables (but can’t in Oracle) A department closes. What should happen to all the foreign keys in related tables? Will need to change them all to the new department number before deleting the department

  43. creating tables Examples of preferred action on updates An employee leaves the company and his details are deleted. What should happen to details about his dependents the details of the hours of the projects he worked on the employees that he supervised the department that he managed We discover that an employee has the wrong staff number. What should happen to all the foreign keys in related tables? A department closes. What should happen to all the foreign keys in related tables?

More Related