Database Systems

1. Database Systems Marcus Kaiser School of Computing Science Newcastle University

2. Security • if a client wishes to access a database it sets up a session • only registered users are allowed to set up sessions • authentication takes place before the user can send queries • the Database Server checks each query to ensure that the user is allowed to see the data he’s requesting • e.g. the Head of Personnel connects and issues a query: • what is A.N.Other’s salary • she gets back the answer £20000 • e.g. a secretary in Personnel connects and issues a query: • what is A.N.Other’s salary • he is told that he is not allowed to access that information

3. Security • How to control what users read and write, e.g. - • prevent tellers increasing their own bank balance • restrict who can see salary information • prevent deletion of information • Solution: User privileges • users have usernames that give them privileges • SQL statements are checked to make sure a user has the required privileges

4. User privileges • SQL has 9 types of privilege • The main ones are the ability specify if tables (base & views) & attributes can be subject to the following…. • Select, Insert, Delete, Update • e.g. Staff(id, name, dept, address, salary) • a user in the mail room may only be allowed to select id, name, dept,address • a user in HR may only be allowed to select all attributes, update dept and address, and insert all attributes • head of HR may be allowed to select, insert, delete and update all attributes

5. Granting Privileges • Users can grant privileges to other users GRANT <privilege list> ON <database element> TO <user list> • e.g. GRANT SELECT ON Staff TO npw1,njkw • The database element is usually a table • The privilege list is usually: • select, update, insert, delete • privileges can be qualified with column names • e.g. GRANT SELECT(name,address) ON Staff TO nadb

6. Passing on Privileges • Each privilege has an option: WITH GRANT OPTION • if true then privilege can be passed on to another user • For example: GRANT SELECT ON Staff TO nwp1 WITH GRANT OPTION • This allows a user to pass on a subset of their own privileges to another user • The inverse of GRANT is REVOKE

7. Database Design

8. Designing a Database • Before creating a Database we need to design it • Database design cannot be done purely mechanically but there are techniques which help • The steps are: 1. Decide on what subjects are covered by the database 2. Decide how they are related to each other 3. Decide on what characteristics they have 4. Derive the database tables from the design 5. Create the database tables using database software

9. Decide what Subjects are Covered by the Database Rules: • Look at what the subject is about, rather than the conclusions you want to find • Think about the subject independently of any particular database software (or computing at all) • Don’t design database on computer • Focus on the data you are likely to collect, rather than how a computer will organise the data

10. Decide on what subjects are covered by the DB Subjects are called Entities: • things that hold interest for you in your database • represent a class of things • precise definition • different for every database you create

11. Example: a Crime database • The police want to track cases • When a robbery has happened who is involved? • What was taken? • Did this go to court? • What was the verdict? • What are the Entities (subjects) here? • Police Officer • Stolen Item • Case • Result

12. Example: a Crime database • e.g. The police want to track cases • we begin by drawing the Entities:

13. Decide how the entities are related to one another • Relationships • a significant association between the entities • represented as a diamond • Each relationship has: • a name • a degree (more on this later) • Names • Pick a simple word that encapsulates the relationship • Concentrate on the main entities first: the rest can come later when you understand the problem better

14. Naming relationships

15. The Degree of Relationship • One-to-One • One occurrence of an entity is uniquely related to another entity • e.g: a crime database: each case has one unique result

16. The Degree of Relationship • One-to-Many • an occurrence of one entity can be related many times to another entity • e.g: a crime database: many items can be stolen in one case

17. The Degree of Relationship • Many-to-Many • there can be many occurrences of one entity related to many occurrences of another entity • e.g: a crime database: many officers can work on a case, and an officer can work on many cases

18. Deciding on the Attributes of the Entities • Attributes are details about the state of an entity • They are things we want to know about an entity • Ensure each has a unique name within the Entity • They are usually drawn as spokes on Entity-Relationship diagrams:

19. Attributes: Primary Keys • Make sure that each Entity has an Attribute (or set of Attributes) which allow it to be uniquely identified • This is called the primary key(often called ID, use underscore in the ER model) • If there might be some doubt about whether or not a set of attributes is unique, then add an extra attribute containing a unique number/code. • e.g. for Officers: • Name and DOB may be enough: but the id attribute is guaranteed to be unique

20. Deriving the Database tables from the Design • The Entities are Tables • The Attributes are their columns • Using the database software manual, find out what the possible column types are and decide on the right one for each attribute • Once this is done, the tables can be created • Next any initial data is entered into the tables • Any standard Queries Forms and Reports are designed and implemented • Now the database is ready for use police officer id station dob rank address name

21. Representing Relationships through Foreign Keys • A FOREIGN KEY in one table points to a PRIMARY KEY in another table. • They are used to represent relationships

22. Representing 1-1 Relationships • Assume each table has an id column which is its key • Then we can add a foreign key column to either table • i.e. Add a CaseId column to Result, or a ResultId column to Case

23. Representing 1-many Relationships • Assume each table has an id column which is its key • Then we must add a foreign key column to the table at the many end of the relationship • i.e. Add a Case Id column to the Stolen Item table

24. Representing 1-to-Many Relationships -- Table `StolenItem` CREATE TABLE `StolenItem` ( `stolenItemId` INT UNSIGNED NOT NULL AUTO_INCREMENT , `stolenItemName` VARCHAR(45) NOT NULL , `caseItemId` INT UNSIGNED NOT NULL AUTO_INCREMENT , PRIMARY KEY (`stolenItemId`) );

25. Representing Many-to-Many Relationships • To implement in a relational database, these are split up into two relationships, with a newly created entity intervening

26. Representing Many-to-Many Relationships • In the Police example we create a new entity which matches Cases to Officers:

27. Representing Many-to-Many Relationships -- Table `Officer` CREATE TABLE `Officer` ( `officerId` INT UNSIGNED NOT NULL AUTO_INCREMENT , `officerName` VARCHAR(45) NOT NULL , PRIMARY KEY (`officerId`) ); -- Table `Case` CREATE TABLE `Case` ( `caseId` INT UNSIGNED NOT NULL AUTO_INCREMENT , `caseName` VARCHAR(45) NOT NULL , PRIMARY KEY (`caseId`) ); -- Table `OfficerCase` CREATE TABLE `OfficerCase` ( `Case_caseId` INT UNSIGNED NOT NULL , `Officer_officerId` INT UNSIGNED NOT NULL , PRIMARY KEY (`Case_caseId`, `Officer_officerId`) );

28. Summary • Security: grant access and pass access to others • Primary and foreign keys: unique identifiers • Degree of a relationship:1:1 1:Many or 1:n Many:Many or n:m (create intervening entity) Quick overview to SQL: http://www.w3schools.com/sql/