DATABASE CONCEPTS AND PRACTICAL EXAMPLES

MYSQL PRACTICAL WORK

1. Database Management Class XII Computer Science/IP

2. What is Database? A database (DB)is an organized collection of data. More specifically, a database is an electronic system that allows data to be easily accessed, manipulated and updated. In other words, a database is used by an organization as a method of storing, managing and retrieving information. Modern databases are managed using a database management system (DBMS).

3. Need of database systems: Database systems reduce data redundancy to large extent. Database systems reduce data inconsistency to large extent. Databases facilitate sharing of data. Centralized databases can ensure data security. Integrity can be maintained through databases

4. Relational data Model A Data models define how the logical structure of a database is modeled. Data models define how data is connected to each other and how they are processed and stored inside the system. Relational data Model-Relational Model is the most widely used model. In this model, the data is maintained in the form of a two-dimensional table. All the information is stored in the form of row and columns. The basic structure of a relational model is tables. So, the tables are also called  relations  in the relational model.  Example:  In this example, we have a student table.

5. Terms used in Relational Model Attribute:  Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno , NAME,etc . Relation – In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes. Tuple  – It is nothing but a single row of a table, which contains a single record. Degree:  The total number of attributes which in the relation is called the degree of the relation. Cardinality:  Total number of rows present in the Table.

6. Terminologies: Keys -help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys ensure that you can uniquely identify a table record despite these challenges. PRIMARY KEY  is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can't be a duplicate meaning the same value can't appear more than once in the table. A table cannot have more than one primary key CANDIDATE KEY  is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key. ALTERNATE KEYS  is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key.

7. Foreign Key Foreign keys are the columns of a table that points to the  primary key  of another table. They act as a cross-reference between tables. In next slide stud_id in course table is foreign key as it point out the primary key of student table

8. Example of Foreign key Course_Id   Stu_Id C01 101 C02 102 C03 101 C05 102 C06 103 C07 102  Stu_Id  Stu_Name  Stu_Age 101 Chaitanya 22 102 Arya 26 103 Bran 25 104 Jon 21 Foreign key

9. Referential Integrity A referential integrity constraint is specified between two tables. It states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Thus, any primary key field changes must be applied to all foreign keys, or not at all. In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.

10. MySQL MySQL is the world's most popular open source database. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more. SQL is used in the accessing, updating, and manipulation of data in a database. Its design allows for the management of data in an RDBMS, such as MYSQL. SQL language also used for controlling data access SQL provide many different types of command used for different purposes. SQL commands can be divided into following category: DDL DML TCL

11. Category of SQL command DDL is abbreviation of  Data Definition Language . It is used to create and modify the structure of database objects in database. CREATE – Creates objects in the database ALTER – Alters objects of the database DROP – Deletes objects of the database TRUNCATE – Deletes all records from a table and resets table identity to initial value. DML is abbreviation of  Data Manipulation Language . It is used to retrieve, store, modify, delete, insert and update data in database. SELECT – Retrieves data from a table INSERT –  Inserts data into a table UPDATE – Updates existing data into a table DELETE – Deletes all records from a table

12. Data types in SQL DATA TYPE FROM TO bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807 int -2,147,483,648 2,147,483,647 smallint -32,768 32,767 decimal -10^38 +1 10^38 -1 numeric -10^38 +1 10^38 -1 date Stores a date like June 30, 1991 Char Fixed length string varchar Variable length string