DBMS

1. DBMS

2. Relational Databases…. • Independent Tables • Relationship through primary & secondary keys • SQL • Select: Creates subset of rows that meet specific criteria • Join: Combines relational tables to provide users with information • Project: Enables users to create new tables containing only relevant information • Compatibility with Existing Applications • Database Security • Data Dictionaries

3. What Makes a Database Relational? • A database is relationalwhen files are relatedto each other, such as this Student ID field in the Student file.

5. Relational Database • Data stored in related tables • Table records have same record type • Record = table row • Attributes = table columns (fields) • Uses common key fields for relationships

6. Table and Record Example

7. Database Characteristics Relationship: association between data stored in different tables

8. Designing Databases • Database structure: arrangement of the fields, tables and relationships • Design for ease of access and maintenance in as small a file as possible. • First determine what data must be collected and stored • Next organize data into fields and define how the field is stored • Break data into small fields (firstname, lastname)

9. Data Types • Character • Numeric • Real and integer • Date • Logical • Memo • Image • Calculated field

10. Organizing Table Data • Sort order: order in which records are stored on disk • Queries and updates are faster • Sort key (one or more fields) • Database index: list of keys and associated record numbers • Doesn’t affect the way records are physically stored

11. SQL and Databases • Structured Query Language that works behind the scenes of the database client software • SQL queries consist of: • An action • Database table name • Set of parameters • SELECT tracktitle FROM tracks WHERE tracktitle = “Fly Away”

12. SQL Let’s have a closer look at Structured query language

13. SQL basics • SQL allows you to access a database • SQL executes queries against a database • SQL commands are case independent. • SELECT = select • But column names or DATA are not case independent. • SQL command are named after english words: • Create, select, insert, update ... • It's easy to learn

14. 3 types of SQL commands • 1. Data Definition Language (DDL) commands - that define a database, including creating, altering, and dropping tables and establishing constraints • 2. Data Manipulation Language (DML) commands - that maintain and query a database • 3. Data Control Language (DCL) commands - that control a database, including administering privileges and committing data

15. DDL, DML, DCL, and the database development process

16. Syntax used in these notes • Capitals = command syntax • Lowercase = values that must be supplied by user • Brackets = enclose optional syntax • Each SQL command ends with a semicolon ‘;’ • In interactive mode, when the user presses the RETURN key, the SQL command will execute

17. Data Definition Language: create the tables • Create table syntax CREATE TABLE tablename ( column1data_type [not null] [unique] [column_constraint] , ... [table constraints]); • Example: CREATE TABLE person ( personID varchar(5) NOT NULL, Name varchar(25) NOT NULL, Firstname varchar(15) NULL);

18. How to judge which table to create and what could be the columns (fields)

19. Remember from the Production System Case Study: you are transforming an Entity to a Table

20. Simple entity translation birthday id General Rule: • Create a table with the name of the Entity. • There is a column for each attribute • The key in the diagram is the primary key of the table Actor name address

21. Student name address student id

22. How to Create tables with SQL • The columns needed for each table can be defined using the CREATE TABLE command. The syntax for this is shown in the upcoming figure. These are the seven steps to follow: • 1. Identify the appropriate datatype for each columns, including length and precision • 2. Identify those columns that should accept null values. Column controls that indicate a column cannot be null are established when a table is created and are enforced for every update of the table

23. Creating tables • 3. Identify those columns that need to be UNQUE - when the data in that column must have a different value (no duplicates) for each row of data within that table. Where a column or set of columns is designated as UNIQUE, this is a candidate key. Only one candidate key may be designated as a PRIMARY KEY • 4. Identify all primary key-foreign key mates. Foreign keys can be established immediately or later by altering the table. The parent table in such a parent-child relationship should be created first. The column constraint REFERENCES can be used to enforce referential integrity

24. Creating tables • 5. Determine values to be inserted into any columns for which a DEFAULT value is desired - can be used to define a value that is automatically inserted when no value is provided during data entry. • 6. Identify any columns for which domain specifications may be stated that are more constrained than those established by data type. Using CHECK it is possible to establish validation rules for values to be inserted into the database • 7. Create the table and any desired indexes using the CREATE TABLE and CREATE INDEX statements

25. Simple entity translation and creating table birthday id create table Actor(id varchar(20) primary key, name varchar(40), birthday date, address varchar(100)); Actor name address Relation: Actor (id, name, birthday, address)

26. Table creation General syntax for CREATE TABLE

27. Table creation • The following Fig. Shows SQL database definition commands • Here some additional column constraints are shown, and primary and foreign keys are given names • For example, the CUSTOMER table’s primary key is CUSTOMER_ID • The primary key constraint is named CUSTOMER_PK, without the constraint name a system identifier would be assigned automatically and the identifier would be difficult to read

28. SQL database definition commands for Pine Valley Furniture

29. STEP 1 Defining attributes and their data types

30. STEP2 Non-nullable specifications Note: primary keys should not be null

31. STEP 3 Identifying primary keys This is a composite primary key

32. STEP 4 Identifying foreign keys and establishing relationships

33. STEP 7 Overall table definitions

34. Fig. (a) Order and Order_Line tables

35. Fig. (b) Product and Customer tables

36. The final shape of the DDL in this case Overall table definitions

37. Constraints • NOT NULL : the column cannot take the NULL value • NULL : the column can take the null value (default) • UNIQUE : the column has unique values

38. Example CREATE TABLE person ( personID varchar(5) NOT NULL UNIQUE, Name varchar(25) NOT NULL, Firstname varchar(15) NULL, City varchar(20)

39. DDL: alter table • Modifying the structure of a table. Add a column: ALTER TABLE <tablename> ADD <column_name> <data_type> [DEFAULT <value>] [<constraint>]

40. DDL: drop table • Delete a table DROP TABLE tablename; • It is very easy and fast to type the drop table command -> be careful !!!

41. Data Manipulation Language (DML): Update Operations INSERT - inserts a list of attributes . INSERT INTO PERSON VALUES (008, Tome, Hector, J); DELETE - removes a row from a table. DELETE FROM PERSON WHERE ID = 7; • Attention “DELETE FROM PERSON” will delete the whole table

42. Update Operations UPDATE - changes the value of one or more attributes in a row. • When modifying an attribute that is not a primary key or foreign key, there is usually no danger of violating a constraint. • When modifying a primary key, it is equivalent to doing a delete followed by an insert operation. UPDATE PERSON SET ID = 007 WHERE LastName=‘Tome’ AND FirstName=‘Pat’;

43. Select • Select is used to retrieve data : SELECT [DISTINCT] <column(s)> FROM <table(s)> [WHERE <condition>] [ORDER BY <column(s) [ASC | DESC]>] • In uppercase are the SQL keywords • Between [] optional conditions • Between <> what correspond to your table(s) definition

44. Select all data SELECT FIRSTNAME, SURNAME FROM PERSON Select specific data, ordered SELECT FIRSTNAME, SURNAME FROM PERSON WHERE SURNAME = ‘BOND’ORDER BY FIRSTNAME ASC Select FirstName SurName JAMES KIRK JAMES BOND ... ...

45. PersonID FirstName SurName MidInitial 045 JAMES KIRK T 007 JAMES BOND X TOP SECRET Select Select all data where person firstname is “JAMES” SELECT * FROM PERSON WHERE FIRSTNAME='JAMES' Select all data where person firstname is “JAMES” and surname is “BOND” SELECT * FROM PERSON WHERE FIRSTNAME='JAMES' AND SURNAME = 'BOND'

46. PersonID FirstName SurName MidInitial 045 JAMES KIRK T 007 JAMES BOND X TOP SECRET Select all data where person firstname begins with “JAM” SELECT * FROM PERSON WHERE FIRSTNAME like 'JAM%'

47. More examples: special conditions • Set conditions: <column> [ ] IN (<list of values>) • SELECT firstname FROM person WHERE surname IN ('BOND','KIRK');

48. Union Query: Give me the IDs of all people called ‘Smith’, and those who have published in ‘Science’. SELECT PERSONID FROM PERSON WHERE SURNAME='SMITH UNION SELECT PERSONID FROM PUBLICATION WHERE JOURNAL= 'SCIENCE'

49. Difference Query: Give me the IDs of all people called ‘Jones’who don’t have a paper in a journal. SELECT PERSONID FROM PERSON WHERE SURNAME=‘JONES’ MINUS SELECT PERSONID FROM PUBLICATION • The difference of two tables is a third table that contain those rows that occur in the first table but not in the second.

50. What we have Done in SQL • DDL • CREATE, ALTER & DROP • DML • INSERT, UPDATE, DELETE & SELECT • Variation of SELECT • Simple SELECT from a table with Where Condition • Union • Difference