Introduction to Database PDF

Chapter 4 INTRODUCTION TO DATABASE Definitions Database:  A very large, integrated collection of data.  Models real-world enterprise.  Entities (e.g., students, courses)  Relationships (e.g., Madonna is taking CS564) Database Management System (DBMS) A soft...

Chapter 4 INTRODUCTION TO DATABASE Definitions Database:  A very large, integrated collection of data.  Models real-world enterprise.  Entities (e.g., students, courses)  Relationships (e.g., Madonna is taking CS564) Database Management System (DBMS) A software package designed to store and manage databases.  Examples of Database Applications:  Banking: all transactions  Airlines: reservations, schedules  Universities: registration, grades Purpose of Database Systems  In the early days, database applications were built directly on top of file systems  Drawbacks of using file systems to store data:  Data redundancy and inconsistency  Multiple file formats, duplication of information in different files  Difficulty in accessing data  Need to write a new program to carry out each new task  Data isolation — multiple files and formats  Integrity problems  Integrity constraints (e.g. account balance > 0) become  “buried” in program code rather than being stated explicitly  Hard to add new constraints or change existing ones  Atomicity of updates  Failures may leave database in an inconsistent state with  partial updates carried out  E.g. transfer of funds from one account to another should either complete or not happen at all  Concurrent access by multiple users  Concurrent accessed needed for performance  Uncontrolled concurrent accesses can lead to inconsistencies  E.g. two people reading a balance and updating it at the same time  Security problems Database systems offer solutions to all the above problems Levels of Abstraction Many views, single conceptual (logical) schema and physical schema.  Views describe how users see the data.  Conceptual schema defines logical structure. Sometime we separate between conceptual level and logical level  Physical schema describes the files and indexes used. * Schemas are defined using DDL (Data Definition Language) *data is modified/queried using DML (Data Manipulation Language) Levels of Abstraction  Physical level: describes how a record (e.g., customer) is stored.  Logical level: describes data stored in database, and the relationships among the data. type customer = record customer_id : string; customer_name : string; customer_street : string; customer_city : string; end;  View level: application programs hide details also hide information (such as an employee’s purposes. Instances and Schemas  Schema – the logical structure of the database  Example: The database consists of information about a set of  customers and accounts and the relationship between them)  Analogous to type information of a variable in a program  Physical schema: database design at the physical level  Logical schema: database design at the logical level  Instance – the actual content of the database at a particular time  Analogous to the value of a variable SQL --> Structured Query Language What is SQL? SQL stands for Structured Query Language SQL is a standard language for storing, manipulating and retrieving data in databases. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. Data Definition Language (DDL)  Specification notation for defining the database schema Example: create table account ( account_number char(10), branch_name char(10), balance integer)  DDL commands include CREATE to create a new table or database. ALTER for alteration. DROP to drop a table. RENAME to rename a table. Data Manipulation Language (DML)  Language for accessing and manipulating the data organized by the appropriate data model  DML also known as query language  SQL is the most widely used query language DML commands include INSERT – is used to insert data into a table. UPDATE – is used to update existing data within a table. DELETE – is used to delete records from a database table. SELECT – is used to retrieve information from db table DELETE – is used to delete records from a database table. SELECT – is used to retrieve information from db table Database Terminologies Relation - A table in a Database Tuple/record – A row in a table Field/Attributes – A column in a table Domain – The possible pool of values for a specific column Degree – No. of columns in a table at that instance Cardinality – No. of rows in a table at that instance Keys Keys- It is used to uniquely identify any record in a table or establish relationships between tables. The PRIMARY KEY uniquely identifies each record in a table. Primary key must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key. Candidate Key(s) is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. A table can have multiple candidate keys but only a single primary key. Alternate keys are those candidate keys which are not the Primary key. There can be only one Primary key for a table. They can also uniquely identify tuples in a table, but the database administrator chose a different key as the Primary key. Constraints The UNIQUE constraint ensures that all values in a column are different. It allows one NULL Value. 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. The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified. The CHECK constraint ensures that all the values in a column satisfies certain conditions. create table stud ( Rollno int Primary key, Name varchar(20) NOT NULL, Grade char(1) default ‘A’, Tot int (check Tot>=0 and Tot show databases; + + | Database | + + | mysql | mysql is a database (stores users’ password …) used by system. | test | + + Create a database (make a directory) whose name is MyDB mysql> create database MyDB; Select database to use mysql> use MyDB; Database changed What tables are currently stored in the MyDB database? mysql> show tables; Empty set (0.00 sec) Create Table CREATE TABLE Table_Name (column_specifications) Example mysql> CREATE TABLE student -> ( -> student_ID INT UNSIGNED NOT NULL, -> name VARCHAR(20) NOT NULL, -> major VARCHAR(50), -> grade VARCHAR(5) -> ); Query OK, 0 rows affected (0.00 sec) Student_ID Name Major Grade  Display Table Structure mysql> show tables; + + | Tables_in_MyDB | + + | student | + + 1 row in set (0.00 sec) mysql> describe student; + + + + + + + | Field | Type | Null | Key | Default | Extra | + + + + + + + | student_ID | int(10) unsigned | | |0 | | | name | varchar(20) | | | | | | major | varchar(50) | YES | | NULL | | | grade | varchar(5) | YES | | NULL | | + + + + + + + 4 rows in set (0.00 sec) Modify Table Structure ALTER TABLE table_name Operations mysql> alter table student add primary key (student_ID); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe student; + + + + + + + | Field | Type | Null | Key | Default | Extra | + + + + + + + | student_ID | int(10) unsigned | | PRI | 0 | | | name | varchar(20) | | | | | | major | varchar(10) | YES | | NULL | | | grade | varchar(5) | YES | | NULL | | + + + + + + + 4 rows in set (0.00 sec)  Insert Record INSERT INTO table_name Values (value1, value2, value3, …); Example mysql> INSERT INTO student Values (101, 'Shannon', 'BCB', 'A‘); Query OK, 1 row affected (0.00 sec) Student_ID Name Major Grade 101 Shannon BCB A Retrieve Record SELECT what_columns Student_I Name Major Grade D FROM table or tables WHERE condition 101 Shannon BCB A Example mysql> SELECT major, grade FROM student 102 Mike BBMB A WHERE name='Shannon'; + + + | major| grade| +-------+-------+ 103 Wang MCDB A | BCB | A | +-------+-------+ … … … 1 row in set (0.00 sec) mysql> SELECT * FROM student;  Update Record UPDATE table_name SET which columns to change WHERE condition Example mysql> UPDATE student SET grade='B' WHERE name='Shannon'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM student WHERE name=‘Shannon’; + + + + + | name | student_ID | major | grade | + + + + + | Shannon | 101 | BCB | B | + + + + + 1 row in set (0.00 sec) Delete Record DELETE FROM table_name WHERE condition Example mysql> DELETE FROM student WHERE name='Shannon'; Query OK, 1 row affected (0.00 sec) mysql> DELETE FROM student; Will delete ALL student records!  Drop Table DROP TABLE table_name Example mysql> drop table student; Query OK, 0 rows affected (0.00 sec) Logout MySQL mysq> quit; More Table Retrieval OR mysql> select name from student where major = 'BCB' OR major = 'CS'; COUNT (Count query results) mysql> select count(name) from student where major = 'BCB' OR major = 'CS'; ORDER BY (Sort query results) mysql> select name from student where major = 'BCB' OR major = 'CS‘ ORDER BY name; mysql> select name from student where major = 'BCB' OR major = 'CS‘ ORDER BY name DESC; mysql> select * from student where major = 'BCB' OR major = 'CS‘ ORDER BY student_id ASC, name DESC LIKE (Pattern matching) mysql> select name from student where name LIKE "J%"; DISTINCT (Remove duplicates) mysql> select major from student; mysql> select DISTINCT major from student;  NULL No Value Can not use the usual comparison operators (>, =, != …) Use IS or IS NOT operators to compare with Example mysql> select name from student where project_ID = NULL; Empty set (0.00 sec) mysql> select name from student where project_ID IS NULL; + + | name| + + | Jerry | +-------+ 1 row in set (0.00 sec) Practical Notes Create and access database, Table and query transaction in MYSQL. Practicing in DDL and DML commands minimum (15 commands)