DAY 12 DATABASE CONCEPT Tazin Afrin Tazin Afrinmail

DAY 12: DATABASE CONCEPT Tazin Afrin Tazin. Afrin@mail. wvu. edu September 26, 2013 1

DATABASE • An organized collection of data. • Database supports processes requiring information about that data. • Example : – Address book – Record of all employees of CNN international on their payroll 2

DBMS • Database Management System contains information about a particular enterprise – Collection of interrelated data – Set of programs to access the data – An environment that is both convenient and efficient to use • Examples of DBMS : – My. SQL – SQLite – Microsoft SQL Server – Microsoft Access 3

DBMS • Database Applications: – Banking: all transactions – Airlines: reservations, schedules – Universities: registration, grades – Sales: customers, products, purchases – Manufacturing: production, inventory, orders, supply chain – Human resources: employee records, salaries, tax deductions 4

RELATIONAL DATABASE • A relational database is a collection of data items organized as a set of formally described tables from which data can be accessed easily. • There are relationships between tables. • Example : – Microsoft Access 5

SAMPLE RELATIONAL DATABASE 6

TERMINOLOGY • Record – In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. – Each record represents a set of related data – Every record has the same structure. • Field – Each piece of data in a record is a field – Some fields are required for each record, others are optional 7

RECORDS AND FIELDS Record Field 8

PRIMARY KEY • A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key of the table. – A table can contain only one primary key constraint. 9

FOREIGN KEY • A foreign key is a column or combination of columns that is used to establish and enforce a link between the data in two tables. • It controls the data that can be stored in the another table. • A link is created between two tables when the column that hold the primary key value for one table are referenced by the column in another table. • This column becomes a foreign key in the second table. 10

FOREIGN KEY 11

FOREIGN KEY 12

INDEXES • Indexes are an ordering of a key or other field that is computed on creation and kept up to date as the database is updated • By using the index, the database software is able to quickly retrieve the record given the field value 13

RELATIONSHIPS • By including a the key from one table as a field in a different table, we create a relationship between the two tables • This allows us to link the data between two tables • Relationships enable you to prevent redundant data. • 4 kinds of relationships 14

RELATIONSHIPS One to one One to many 15

RELATIONSHIPS Many to one Many to many 16

REFERENTIAL INTEGRITY • Make sure that relationships between records in related tables are valid. • Ensures that you do not accidentally delete or change related data. • You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. – Cannot enter new account in the account table for a customer who does not exists. – But can enter new account with NULL value in customer ID 17

CASCADING • You can specify whether you want to automatically cascade update or cascade delete related records from different tables. – Deletes: If the original record is deleted, the foreign key record is deleted – Updates: If the key of the original record is changed, the foreign key is updated to match 18

NORMALIZATION • Normalization is a process of organizing fields and tables to minimize redundancy of data – DRY (don’t repeat yourself) – If you repeat yourself, when you need to make a change you have to change it everywhere or you will have problems 19

SQL • SQL is structured query language • SQL is how Access interacts with data under the hood • Queries: – INSERT INTO ‘table’ VALUES (‘value 1’, ‘value 2’) – UPDATE ‘table’ SET ‘field 1’ = ‘value 1’ WHERE ‘field 2’ = ‘value 2’ – DELETE FROM ‘table’ WHERE ‘field’ = ‘value’ – SELECT ‘field 1’ FROM ‘table’ WHERE ‘field 2’ = ‘value’ 20

EXAM 1 • Student ID is required • If you need accommodations, please make your request today. • Arrive 15 minutes early • 10 minutes before scheduled class time, test instructions will be distributed • You may begin at your scheduled class time • You will have 60 minutes 21

EXAM 1 • 1 st October • Section 58 : at 1: 00 pm • Section 60 : at 2: 30 pm 22

THANK YOU LOG OFF