Databases MIS 21 Some database terminology Database integrated

Databases MIS 21

Some database terminology Database: integrated collection of data p Database Management System (DBMS): environment that provides mechanisms for storage and access of data p Relational Database Model: views a database as a collection of relations or tables where each table stores information on an entity p

Tables, attributes and columns A table represents an entity in a system p The rows of the table represent records or instances of the entity p The columns of the table represent the entity’s attributes or characteristics p p How do you determine a system’s entities? n Spot the nouns (people, places, things) in the system

Example: The STUDENT table Columns/Attributes ID Last. Name First. Name Year QPI 081111 Cruz Juan 2 3. 5 072222 Smith John 3 2. 2 089999 Cruz Maria 3 4. 0 077676 Santos Linda 4 3. 0 There are four rows/records in this example

Column types/domains Each column in a table has an associated type indicating the possible values for the attribute p Most common types p n n n Strings: CHAR, VARCHAR Numbers: INTEGER, DOUBLE, NUMERIC Date and time: DATE, TIMESTAMP

Primary key, foreign key, relationships p Primary key: column or attribute that uniquely determines a row in the table n p STUDENT table example: ID is the primary key Foreign key: attribute in one table that refers to a record in another table n n Used to store relationships (relationships are verbs in the system) Example: a department code in the EMPLOYEE table refers to the DEPARTMENT table, signifying that the employee belongs to the department

Relationship example EMPLOYEE Emp. ID Name Salary Dept. Code 123 CRUZ, JUAN 12345. 00 HR 222 SANTOS, MARIA 11111. 00 HR 545 MATIPID, PETE 30000. 00 ACCT DEPARTMENT Employee SANTOS, MARIA works for the PERSONNEL department Dept. Code Dept. Name ACCT ACCOUNTING HR PERSONNEL MAINTENANCE

Structured Query Language p SQL: Structured Query Language n n p Also called “Se. Que. L” Standard underlying language for database definition, manipulation, and query SQL statements n n n SELECT (query) INSERT UPDATE DELETE Others (Data Definition)

The SELECT statement A query that returns a table p Simplest form: SELECT column 1, column 2, … FROM table p n p List all records, all columns SELECT * FROM table n p SELECT Emp. ID, Salary FROM EMPLOYEE SELECT * FROM DEPARTMENT List particular record/s SELECT * FROM table WHERE criteria n SELECT * FROM STUDENT WHERE year=3

The WHERE clause p p Indicates selection criteria or condition against which records on the table will be checked Contains operators such as <, >, <=, >= =, <> and LIKE n n n p <> means not equal LIKE performs a pattern match with strings ( _ matches a single character, % matches several characters) Condition may have logical operators (AND, OR, NOT) Note the importance of the column type when specifying the condition n Literal strings (e. g. , CHAR) need to be delimited by quotes or double quotes

ORDER BY clause Can indicate ORDER BY in the SELECT statement to specify ghe order in which the records will be listed p Syntax: append ORDER BY col 1, col 2, … to the SELECT statement p Indicate ORDER BY col 1, col 2, … DESC to list records in descending order p

More examples p p p SELECT Name FROM EMPLOYEE WHERE SALARY > 12000 SELECT First. Name, Lastname FROM STUDENT WHERE Last. Name LIKE ‘S%’ SELECT * FROM STUDENT WHERE year <> 2 SELECT * FROM STUDENT WHERE year=3 ORDER BY Last. Name, First. Name SELECT Name FROM EMPLOYEE ORDER BY Salary

SELECT on multiple tables p p FROM clause can contain multiple tables Use when processing relationships Tables are joined; indicate join condition in WHERE clause Example: SELECT Name, Dept. Name FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE. Dept. Code = DEPARTMENT. Dept. Code AND Salary >10000 returns all names of employees (and the name of the departments they belong to) who make more than 10000

The INSERT Statement Inserts a record into a table p Syntax: p n n p INSERT INTO table VALUES(val 1, val 2, …) INSERT INTO table(col 1, col 2, …) VALUES(val 1, val 2, …) Examples: n n INSERT INTO DEPARTMENT VALUES(“CS”, “COMPUTER SCIENCE”) INSERT INTO EMPLOYEE(Emp. ID, Salary) VALUES(143, 10000. 00)

The UPDATE Statement Updates an existing record p Syntax: p n n p UPDATE table SET col 1=expr 1, col 2=expr 2, … WHERE criteria Examples n n (updates all records) UPDATE EMPLOYEE SET Salary=Salary+100 (updates some record/s) UPDATE STUDENT SET QPI = 4. 0 WHERE First. Name = “Matalino”

The DELETE Statement Deletes records from a table p Syntax: p n p DELETE FROM table WHERE criteria (warning: without a WHERE clause, all records are deleted) Example n DELETE FROM EMPLOYEE WHERE Dept. Code=“CS”

Summary A relational database consists of tables that store records of a system p The database is manipulated through SQL, the underlying query and manipulation language of relational databases p SELECT statements carry out queries p INSERT, UPDATE, and DELETE statements affect the database p