Data base intro:
Problems of excel: redundancy and multiple themes
Create modification problems: deletion problems, update problems, insertion problems
Relational databases : stores info in tables
Will break up a list into several parts; one for each theme
Minimizes data redundancy, preserves complex relationships among topics, and allows
for partial data
Components of database system:
Users:
o use a database application to track things
o use forms to enter, read, delete and query data
o produce reports.
Database application: set of one or more computer programs that serve as an
intermediary between user and DBMS
o Create and process forms/reports
o Process user queries
o Execute application logic
o Control databases application
Database management system DBMS
o Intermediary between database application and database
o Manage and control database activities
o Read and modify database (insert,update,delete, create tables)
o Creates, processes, and administers the databases
o Perform backup and recovery
o Enforce rule and referential integrity constraints that ensures that values of one
table are valid based on values in another
Personal database systems: 1 application, 1 user, 1 computer , few tables
Entreprise class database system: complex, multiple app, computers, and
users
Database : self-describing collection of related records
o Contains definition of its structure
o Metadata = describes the nature of database data
o Tables with a relational database
Data base concepts:
An entity represents one theme/topic
o in an entity-relationship model entities are restricted to things that can be
represented by a single table
in a relation (table):
o rows contain data about entity
o columns contains data about attributes of the entity
o cells hold a single value
o all entries in a column are of the same type
o order of columns/rows is unimportant
o no 2 rows can be identical
A key is a column(s) used to identify a row
o Unique key : unique for each row (ex; ur student id)
o Non-unique key: identify a set of rows shared by it (ex; your first name)
o Composite key: contains 2 or more attributes non unique to give unique value
o Candidate key: unique key – could be a primary key
o Primary key: main key for relation
o Surrogate key: unique, numeric value that is added to a relation to serve as the
primary key
o Foreign key: a primary key used in another table so it becomes foreign
Referential integrity : value of foreign key must match a value of a am
existing primary key
Functional dependency: relationship where one or group of attributes (determinants)
determine the value of another attribute
o A primary/candidate key of a relation will functionally determine all other
attributes in the row
SQL: Structured query language
DDL: data definition language : to define databases structure
o CREATE: to create database objects
o ALTER: modify structures or characteristics of database objects
o DROP: delete database objects
o TRUNCATE: delete table data while keeping the structure
DML: data manipulation language: data definition and updating/ retrieval
o SELECT … FROM
Use SELECT * to show all column values
Use SELECT DISTINCT to not have duplicate rows
Ex; SELECT DeptId, FirstName, LastName
FROM Employee
WHERE EmpID= 20122 AND DeptID > 7 ;
o WHERE : match criteria
=,<>,<,>,<=,>=
AND/ OR
BETWEEN … AND …
IN () / NOT IN () Ex; WHERE DeptID IN (2,3,4)
LIKE *, ?, # Ex; WHERE Name LIKE “K*”// WHERE Numb
LIKE “713-###-###”// WHERE Name LIKE “M??”
o ORDER BY … ASC/DESC
o COUNT/MAX/MIN/SUM/AVG … AS …
GROUP BY… HAVING
Ex; SELECT MIN(Hours)AS MinimumHours,
FROM Employee
GROUP BY DeptID HAVING MIN(Hours)>1;
When we need to retrieve info from multiple tables, we have 3 methods:
Join function
Ex;SELECT EmpName
FROM Employee AS E, Department AS D
WHERE [Link] = [Link]
AND [Link] LIKE “Account *”;
Here, we select what we want to retrieve, and then rename the tables. We specify in
WHERE function the equal field (where the tables intersect).
Inner join function
Ex;SELECT EmpName
FROM Employee AS E INNER JOIN Department AS D
ON [Link] = [Link]
WHERE [Link] LIKE “Account *”;
Creating a subquery:
Ex; SELECT EmpName
FROM Employee
Where EmpID in (
SELECT EmpID
FROM Department
WHERE DepName LIKE “Account *”;
Modifying data:
INSERT INTO table(field1, field 2,…) VALUES (v1,v2,…) : adds a row
Ex;INSERT INTO Employee(Name, age) VALUES (“John”,19)
Or Ex; INSERT INTO Employee VALUES (“John”,19)
UPDATE … SET… WHERE : updates a value in existing row
Ex; UPDATE Employee
SET Phone = ‘791-233’
WHERE EmpID= 9
DELETE FROM … WHERE : deletes a row
Ex; DELETE FROM Employee
WHERE EmpID= 9