DATABASE LANGUAGES; DDL, DML, TCL, ETC..

A COMPLETE LECTURE TO UNDERSTAND DATABASE LANGUAGES.

1. CSC355- Database Systems Week-3 Lecture 1 & 2 Semester 4- Spring 2019 Lahore Garrison University 1

2. Database Language Database languages are the set of statements that are used to define and manipulate a database.

3. Data Definition Language (DDL) Language that specifies the database schema by set of definitions DDL basically, constructs a database DDL uses special language, Data Storage and Definition Language (DSL) , to specify the storage structure and access methods. It defines the physical schema, but today the physical schema is constructed by using combination of functions and parameters The values that are stored in database are required to satisfy certain constraints Lahore Garrison University 3

4. DDL Constraints Lahore Garrison University 4

5. Domain Constraint Domain Constraints: Whenever we define any attribute in database, we must specify its domain. Domain Constraint is the set of valid values for an attribute. For Example: If we specify an attribute name in a table. Now by specifying it as “ Name char (25)” would restrict the attribute to take char value only. So domain constraint can be defined as the data type that an attribute can take. Lahore Garrison University 5

6. Referential Constraints: Referential Constraints: Tables in database are not independent. There are certain links between the tables of database. These links are created by referential constraints. Foreign key defines the referential constraints in a table For Example: Here, department id functions as a foreign key. As it belongs to department table. It creates a reference between department and student Lahore Garrison University 6 S_id D_id S_name S_age 1 2 Ali 23 2 4 Ahmad 25

7. Assertion Constraints A constraint that must always be satisfied in a database. An  assertion  is a predicate expressing a condition we wish the database to always satisfy. For Example: A student record must have roll number in it. Its roll number can never be NULL. Lahore Garrison University 7

8. Authorization Constraints We cannot allow every user to access and modify the database. So some authorization constraints are introduced. These constraints gives the user some access while revoke others Lahore Garrison University 8 Constraints Description Read Authorize the users to read data from database Insert Authorize the user to add new data Update Authorize the users to modify data in database Delete Authorize the users to delete data from database

9. Set of statements in DDL to Implement Database Command Function Create To create a relation in database Alter To reconstruct data in database Truncate To delete entries from relation but keeping the structure of relation in database Drop To delete a relation from database Rename To rename a relation in database Lahore Garrison University 9

10. Data Manipulation Language Set of statements that allows user to access and manipulate data in database. It is used to insert, retrieve, modify or delete data from database Lahore Garrison University 10

11. Procedural DML In procedural languages, the program code is written as a sequence of instructions. User has to specify “what to do” and also “how to do” These instructions are executed in the sequential order. Procedural DML is embedded into a high-level programming language.

12. Non-Procedural DML In the non-procedural languages, the user has to specify only “what to do” and not “how to do”. It is also known as an applicative or functional language. Lahore Garrison University 12

13. DDL vs. DML Lahore Garrison University 13

14. Database Language (DML) Fourth Generation Languages (4GLs) Forms generators: A forms generator is an interactive facility for rapidly creating data input and display layouts for screen forms. Report generators: A report generator is a facility for creating reports from data stored in the database. Graphics generators: A graphics generator is a facility to retrieve data from the database and display the data as a graph showing trends and relationships in the data. Application generators: An application generator is a facility for producing a program that interfaces with the database. Lahore Garrison University 14

15. Data Model Integrated collection of concepts for describing data, relationships between data , and constraints on the data in an organization. Data Model comprises: a structural part consisting of a set of rules according to which databases can be constructed ; a manipulative part defining the types of operation that are allowed on the data (this includes the operations that are used for updating or retrieving data from the database and for changing the structure of the database); possibly a set of integrity rules which ensures that the data is accurate.

16. Data Model Purpose To represent data in an understandable way. Categories of data models include: Object-based Record-based Physical.

17. Record Based Data Models Record Based data models defines the actual relationship between the data in the entities Lahore Garrison University 17

18. Relational Data Model It represent the database as a collection of relations. In database, a table is termed as a relation Relational data model is proposed by E.F.Codd After designing the conceptual model using ER Diagram, it is converted to relational model using RDBMS languages like Oracle, My SQL etc Lahore Garrison University 18

19. Terminologies Attributes: Properties that defines a relation like age, roll number, name etc. Columns in tables are often referred to as attributes Table: Relation in a relational database or relational model is termed as table. The table have 2 properties row and columns. Rows represent records and columns represent attributes Tuple: Single row of a table is known as tuple Relation Schema: Represent name of the table along with its attributes. If there is more than 1 relation the schema is known as relational schema Lahore Garrison University 19

20. Degree: Total number of attributes table have represents degree Cardinality: Total number of rows in table represents cardinality Relation Instance: Finite set of tuple in RDBMS. Relation instance cannot have duplicate tuples Relation Key: Each row have one or more attributes known as relation key which uniquely identify row in table Attribute Domain: The pre-defined value scope of attribute Lahore Garrison University 20

21. Example: Student In the above example: Lahore Garrison University 21 Roll_number Name Address Phone Age 123 Ali Lahore 1234567 23 456 Ahmed Islamabad 7689021 24 789 Rimsha Karachi 43567340 27 001 Adnan Quetta 3456789 29 Attributes are Roll_number , name, address, phone, age Relation Schema Student ( Roll_number , name, address, phone, age) Tuple 123 Ali Lahore 1234567 23 Degree 5 Cardinality 4

22. Relational Data Model

23. Constraints IN RDM Key Constraint: There must be a minimal subset of attribute that uniquely identify a tuple. This subset is known as key. It is also known as Entity Constraints. Properties: It should be unique for all tuples Cannot have NULL value Domain Constraint Referential Integrity Constraint Lahore Garrison University 23

24. Network Data Model

25. Hierarchical Data Model

26. Object based data model In object based data model the focus is on how data is represented. The data is divided into different entities each of which have some characteristics The entities are connected with each other through a relationships. These entities are based on real world models Lahore Garrison University 26

27. Lahore Garrison University 27

28. Entity Relationship Data Model It represents real life scenarios as an entity. The properties of these entities are their attributes. Entity: real world thing. It can be a person, place or a concept. Like teacher, student, building etc Attributes: Properties if these entities like salary of a teacher, name or age of a person Relationship: tells how the entities are connected. Like teacher works for department. Here, works for is a relationship between teacher and department Lahore Garrison University 28

29. Lahore Garrison University 29 Teacher Department Works for name salary Mobile Number id Department name