DBMS Structure of Relational Databases.pptx

Structure of Relational Database

1. Structure of Relational Databases Concepts of a table, a row, a Relation, a Tuple and a key in a relational database Conversion of ER to Relational model Integrity constraints (primary key, referential integrity, unique constraint, Null constraint, Check constraint) Case studies

2. Structure of Relational Databases A relational database consists of a collection of tables. Each database table is formed using set of rows and columns. Each table is assigned a unique name. E.g. student table which stores the information of students such as PRN, name, contact, address, email, date of birth etc. Table student has 6 column headers. Each row of this table will hold the information of the students with unique records based on key. In the relational model the term relation is used to refer to a table. The term tuple is used to refer to a row, one complete record.

3. Keys & Super key No two tuples in a relation are allowed to have exactly the same value for all attributes. Super key A super key is a set of one or more attributes that taken collectively, allow us to uniquely identify a tuple in the relation. E.g. PRN in the student table

4. Candidate key A super key may contain extraneous attributes. For example, the combination of PRN and name is a super key for the relation student. For some relations there are more than one attribute to designate as a super key. Such minimal super keys are called candidate keys.

5. Conversion of ER to Relational model After designing the ER diagram of system, we need to convert it to Relational models which can directly be implemented by any RDBMS like Oracle, MySQL etc.   Binary Relationship with 1:1 cardinality with total participation of an entity.

6. Conversion of ER to Relational model A person has 0 or 1 passport number and Passport is always owned by 1 person, hence it is 1:1 cardinality. First convert each entity and relationship to tables. Person table corresponds to Person Entity with key as Per-Id. Similarly Passport table corresponds to Passport Entity with key as Pass-No. Has Table represents relationship between Person and Passport (Which person has which passport). So it will take attribute Per-Id from Person and Pass-No from Passport. 

7. Conversion of ER to Relational model Person Has Passport Per-Id Other Person Attributes Per-Id Pass-No Pass-No Other Passport Attributes PR1 - PR1 PS1 PS1 - PR2 - PR2 PS2 PS2 - PR3 - PR3 PS3 - PR4 - PR4 PS4 - PR5 - PR5 PS5 PS5 - Pass-No can’t be key because for some person, it can be NULL. 

8. Create table command create table command of Structured Query Language (SQL) is used to create table. Structured Query Language (SQL) is a standard language for storing, manipulating and retrieving data in databases. create table <table name> (colname1 data_type (length) , colname2 data_type (length) , colname3 data_type (length) , colnameN data_type (length) , integrity constraint ( colname ))

9. Data Types SQL Basic Data Types The char data type is used to store the character values. It is a fixed-length data type i.e once initialized we cannot change the size at execution time. Hence, it is also called a Static datatype. The char data type can store a character string of a maximum length of 2000 bytes of characters. for every character, one byte is stored in the memory. Since the size is fixed and cannot be changed, it will lead to memory wastage.

10. Data Types SQL Basic Data Types: varchar(n) : A variable-length character string with user-specified maximum length n. The full form is character varying. The varchar data type is used to store the character values. It is a variable-length data type i.e we can change the size of the character at the time of the execution. Hence, it is also called a Dynamic datatype. It is used to store normal characters and alphanumeric characters too. The varchar data type can store a character string of a maximum length of 4000 bytes. Also, for every one character, one byte is stored in the memory. int: An integer (a finite subset of the integers that is machine dependent). The full form, integer, is equivalent.

11. Integrity constraints: primary key, referential integrity, unique constraint, Null constraint, Check constraint Primary Key Primary key used to ensure the uniqueness in the given table. It is a key which identify all records of the relation uniquely. Primary keys must contain UNIQUE values and cannot contain NULL values. The primary key should be chosen such that its attribute values are never, or very rarely changed. For instance, the address field of a person should not be part of the primary key, since it is likely to change.

12. Primary Key Constraint The PRIMARY KEY constraint uniquely identifies each record in a table. A table can have only ONE primary key. The primary key can consist of single or multiple columns (fields). The following SQL statement creates a PRIMARY KEY on the “ PersonID " column when the "Persons" table is created: MySQL: CREATE   TABLE  Persons (     PersonID int  NOT   NULL ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int,      PRIMARY   KEY  ( PersonID ) );

13. SQL PRIMARY KEY on CREATE TABLE SQL Server / Oracle / MS Access: CREATE   TABLE  Persons (     PersonID int  NOT   NULL   PRIMARY   KEY ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int );

14. SQL PRIMARY KEY on CREATE TABLE To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access: CREATE   TABLE  Persons (     PersonID int  NOT   NULL ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int,      CONSTRAINT   PK_Person   PRIMARY   KEY  ( PersonID,LastName ) );

15. SQL PRIMARY KEY on ALTER TABLE To create a PRIMARY KEY constraint on the “ PersonID " column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access: ALTER   TABLE  Persons ADD   PRIMARY   KEY  ( PersonID ); To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access: ALTER   TABLE  Persons ADD   CONSTRAINT   PK_Person   PRIMARY   KEY  ( PersonID,LastName );

16. DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY constraint, use the following SQL: MySQL: ALTER   TABLE  Persons DROP   PRIMARY   KEY ; SQL Server / Oracle / MS Access: ALTER   TABLE  Persons DROP   CONSTRAINT  PK_Person;

17. Foreign key A table/relation, T1, may include among its attributes the primary key of another table/relation, T2. This attribute is called a foreign key from T1, referencing T2. The relation T1 is also called the referencing relation of the foreign key dependency, and T2 is called the referenced relation.

18. SQL FOREIGN KEY on CREATE TABLE SQL FOREIGN KEY on CREATE TABLE The following SQL creates a FOREIGN KEY on the " PersonID " column when the "Orders" table is created: MySQL: CREATE   TABLE  Orders (     OrderID int  NOT   NULL ,     OrderDescription varchar(50)   NOT   NULL ,     PersonID int,      PRIMARY   KEY  ( OrderID ),      FOREIGN   KEY  ( PersonID )  REFERENCES  Persons( PersonID ) ); SQL Server / Oracle / MS Access: CREATE   TABLE  Orders (     OrderID int  NOT   NULL   PRIMARY   KEY ,     OrderDescription varchar(50)   NOT   NULL ,     PersonID int  FOREIGN   KEY   REFERENCES  Persons( PersonID ) );

19. SQL FOREIGN KEY on ALTER TABLE To create a FOREIGN KEY constraint on the " PersonID " column when the "Orders" table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access: ALTER   TABLE  Orders ADD   FOREIGN   KEY  ( PersonID )  REFERENCES  Persons( PersonID );

20. DROP a FOREIGN KEY Constraint To drop a FOREIGN KEY constraint, use the following SQL: MySQL / SQL Server / Oracle / MS Access : ALTER   TABLE  Orders DROP   FOREIGN   KEY   PersonID ;

21. Referential integrity A referential integrity constraint requires that the values appearing in specified attributes of referencing relation also appear in specified attributes of at least one tuple in the referenced relation. A referential integrity constraint is also known as  foreign key constraint . A foreign key is a key whose values are derived from the Primary key of another table. The table from which the values are derived is known as  Master or Referenced  Table and the Table in which values are inserted accordingly is known as  Child or Referencing  Table In other words, we can say that the table containing the  foreign key  is called the  child table , and the table containing the  Primary key/candidate key  is called the  referenced or parent table .

22. Referential integrity The syntax of the Master Table or Referenced table is: CREATE TABLE Student (Roll int PRIMARY KEY, Name varchar(25) , Course varchar(10) );   The syntax of Child Table or Referencing table is: CREATE TABLE Subject (Roll int references Student,  SubCode  int,  SubName  varchar(10) );  In the Subject table, column Roll is acting as Foreign Key, whose values are derived using the Roll value of Primary key from Master table Student.

23. Foreign Key Constraints/Referential integrity constraints: There are two referential integrity constraint: Insert Constraint:  Value cannot be inserted in CHILD Table if the value is not lying in MASTER Table. Delete Constraint:  Value cannot be deleted from MASTER Table if the value is lying in CHILD Table.

24. Unique Constraint A  unique constraint is the rule that the values of a key are valid only if they are unique. The unique key ensures that all values in a column are different. Primary key constraint automatically has unique constraints . A key that is constrained to have unique values is called a unique key. There are two types of unique constraints: Unique keys can be defined as a primary key using a CREATE TABLE or ALTER TABLE statement. A base table cannot have more than one primary key. Unique keys can be defined using the UNIQUE clause of the CREATE TABLE or ALTER TABLE statement. A base table can have more than one set of UNIQUE keys.  Multiple null values are allowed. A unique key that is referenced by the foreign key of a referential constraint is called the parent key. A parent key is either a primary key or a UNIQUE key.

25. Unique Constraint SQL UNIQUE Constraint on CREATE TABLE In MySQL CREATE   TABLE  Persons (     ID int  UNIQUE ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int     );

26. Unique Constraint Unique Constraint on multiple columns MySQL / SQL Server / Oracle / MS Access: CREATE   TABLE  Persons (     ID int  NOT   NULL ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int,      CONSTRAINT   UC_Person   UNIQUE  ( ID,LastName ) );

27. Unique Constraint SQL UNIQUE Constraint on ALTER TABLE We can alter the table with unique constraint ALTER   TABLE  Persons ADD   UNIQUE  (ID); To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax: ALTER   TABLE  Persons ADD   CONSTRAINT   UC_Person   UNIQUE  ( ID,LastName );

28. DROP a UNIQUE Constraint To drop a UNIQUE constraint, use the following SQL: MySQL: ALTER   TABLE  Persons DROP   INDEX   UC_Person ; SQL Server / Oracle / MS Access: ALTER   TABLE  Persons DROP   CONSTRAINT  UC_Person;

29. Difference between Primary Key Constraint and UNIQUE Constraint Primary Key Constraint Unique Constraint Used to serve as a unique identifier for each row in a table. Uniquely determines a row that isn’t the primary key. Primary key column cannot accept NULL values. Unique key column can accept NULL values. In a given relation/table we can specify only one primary key In a given relation/table we can specify more than one unique key A primary key supports auto-increment value. A unique key does not support auto-increment value. We cannot change or delete values stored in primary keys. We can change or delete unique key values. Example: CREATE   TABLE  Persons (     PersonID int  NOT   NULL   PRIMARY   KEY ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int ); Example: CREATE   TABLE  Persons (     ID int  UNIQUE ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int     );

30. SQL  NOT NULL  Constraint SQL NOT NULL Constraint By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

31. SQL NOT NULL on CREATE TABLE The following SQL ensures that the "ID", " LastName ", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created: CREATE   TABLE  Persons (     ID int  NOT   NULL ,      LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 )  NOT   NULL ,     Age int );

32. SQL NOT NULL on ALTER TABLE To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL: SQL Server / MS Access: ALTER   TABLE  Persons MODIFY   COLUMN  Age int  NOT   NULL ; My SQL / Oracle ALTER   TABLE  Persons MODIFY   COLUMN  Age int  NOT   NULL ; Oracle 10G and later: ALTER   TABLE  Persons MODIFY  Age int  NOT   NULL ;

33. SQL  CHECK  Constraint The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column.

34. SQL CHECK on CREATE TABLE The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that the age of a person must be 18, or older: MySQL: CREATE   TABLE  Persons (     ID int  NOT   NULL ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int,      CHECK  (Age>= 18 ) );

35. SQL CHECK on CREATE TABLE SQL Server / Oracle / MS Access: CREATE   TABLE  Persons (     ID int  NOT   NULL ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int  CHECK  (Age>= 18 ) ); To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access: CREATE   TABLE  Persons (     ID int  NOT   NULL ,     LastName varchar( 255 )  NOT   NULL ,     FirstName varchar( 255 ),     Age int,     City varchar( 255 ),      CONSTRAINT   CHK_Person   CHECK  (Age>= 18   AND  City= ‘Doha' ) );

36. SQL CHECK on ALTER TABLE To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access: ALTER   TABLE  Persons ADD   CHECK  (Age>= 18 ); To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access: ALTER   TABLE  Persons ADD   CONSTRAINT   CHK_PersonAge   CHECK  (Age>= 18   AND  City= ‘Doha' );

37. DROP a CHECK Constraint To drop a CHECK constraint, use the following SQL: SQL Server / Oracle / MS Access: ALTER   TABLE  Persons DROP   CONSTRAINT  CHK_PersonAge; MySQL: ALTER   TABLE  Persons DROP   CHECK   CHK_PersonAge ;