Please enable JavaScript.
Coggle requires JavaScript to display documents.
Database Management (SQL (IS NULL
SELECT *
FROM tblAgreement
WHERE…
Database Management
SQL
- IS NULL
SELECT *
FROM tblAgreement
WHERE EndDate IS NULL;
- IS NOT NULL
SELECT *
FROM tblAgreement
WHERE EndDate IS NOT NULL;
- Text Conditions in single quotes
SELECT Name, Street, Town
FROM tblCustomer
WHERE Name = 'Elrond';
- Attributes have full names
tableName.attributeName
- WHERE
SELECT Street, Town, PostCode
FROM tblCustomer
WHERE CustomerNo = 1;
- Date Format in single quotes
'1 Sep 2009'
- Not equal to <>
SELECT *
FROM tblPhone
WHERE Cost<>100;
- BETWEEN lower and upper limit (inclusive)
SELECT *
FROM tblPhone
WHERE Cost BETWEEN 50 AND 80;
- LIKE 'E%' begins with E
SELECT *
FROM tblCustomer
WHERE Name LIKE 'E%';
- IN Hobbiton or Moria
SELECT *
FROM tblCustomer
WHERE Town IN ('Hobbiton','Moira');
- OR
SELECT Name,Street, Town
FROM tblCustomer
WHERE Name='Elrond' OR Name='Sam';
- AND
SELECT *
FROM tblCustomer
WHERE DOB < '01 Jan 1970'
AND town = 'Hobbiton';
- Combined AND & OR
SELECT *
FROM tblAgreement
WHERE (ContractTypeNo <> 2)
AND (StartDate = '03 Apr 08' OR StartDate = '14 May 08');
- ORDER BY attribute DESC, (WHERE optional)
SELECT *
FROM tblContractType
WHERE ContractTypeNo <> 1
ORDER BY MonthlyCost DESC;
- Order attributes and order related attributes
SELECT ContractNo, CustomerNo, ContractTypeNo
FROM tblAgreement
ORDER BY ContractTypeNo, CustomerNo;
- Queries on a 3-table join
SELECT tbCustomer.Name, tblAgreement.PhoneNo
FROM tblCustomer, tblAgreement, tblContract
WHERE tblCustomer.CutomerNo = tblAgreement.CustomerNo
AND tblContractType.ContractTypeNo = tblAgreement .ContractTypeNo
AND tblContractType.PlanDescription = 'Pay As You Go'
AND tblAgreement.ModelNo = 'SM-C115';
- Aggregate functions MAX( ), MIN( ), AVG( ), COUNT( ), SUM( )
SELECT MAX (MonthlyCost)
FROM tblContractType;
GROUP BY & HAVING - acts on group , WHILE acts on row)
SELECT CustomerNo, COUNT(CustomerNo)
FROM tblAgreement
GROUP BY CustomerNo
HAVING COUNT (CustomerNo) > 2;
- DELETE - without WHERE will delete whole table
DELETE FROM tblCustomer
WHERE CustomerNo = 2;
- UPDATE
UPDATE tblCustomer
SET Street = '25 High Road', Town ='Bree'
WHERE Name = 'Sam';
1:M - Optional : Obligatory [POST]
Every machine has exactly one employee. 
Employee(Employee#, ...)Machine(Machine#, Employee# , ...)
1:M - Optional : Optional [NEW TABLE]
Employee (Employee#, …….)
Machine (Machine#, ……..) Works_on (Machine#, Employee# , ……..)
- PARTICIPATION CONDITION (Membership Class)
Type of participation of an entity in a relationship:
- Obligatory - a dot inside the entity box means there must be at least 1
- Optional - a dot outside the entity box means zero is possible (
- Participation conditions are about minimum numbers.
- Usually question 'Does the entity have to ...? '
- DEGREE The degree is shown by the use of 1 or M on the relationship
- The degree of a relationship gives maximum numbers
- Usually question 'How many times can the entity... 1 or many?'.
- MULTIPLICITY
- Shorthand for “degree and participation condition”.
- Multiplicity covers both upper and lower limits eg 0…1, 1…..M.
DECOMPOSITION OF M:M RELATIONSHIPS
A M:M relationship between 2 entity types must be decomposed into two 1:M relationships.
- Estate Agent Example
- More than one relationship between two entities possible e.g. customer and house:
* Recursive relationships
- An individual entity can have a relationship with an entity of the same type
1:1 Optional:Obligatory [POST]
Every machine has exactly one employee,
so we can post the employee number into the Machine tableEmployee (Employee#, …….)Machine (Machine#, Employee# , …....) :
1:1 Optional:Optional [NEW TABLE]
We need a seperate table for those that take part seperating them from those that don't.Employee (Employee#, …….)
Machine (Machine#, …….., )Works_on (Employee#, Machine# , ……..)
1:1 Obligatory:Obligatory [COLAPSE]
Every employee works on exactly one machine, and every machine
is worked on by exactly one employee We could post both ways, collapsed into one table:
Employee (Employee#, employee_name,….., Machine#, machine_location……..)
3 LAYER ARCHITECTURE
-
- 3. User View - External Layer/Level
- DB creates user views (of permitted content) from logical layer items and present to specific users.
- External view describes the segment of the database that is required for a particular user group and hides the the database from that user group.
- 2. Conceptual/Logical Layer/Level
- DB organises info. for user requirements.
- Conceptual schema is a representation of the entire content of the DB.
- Describes whole DB structure for a group of users
- Called the data model.
- Schema contain information to build external records
- Hides details of physical storage
- Logical Data Independance
- Ability to change the logical schema without affecting external/physical schema
- Add, remove, change table(object) or attributes and values
- 1. Internal/Physical Layer/level
- OS & DB work to manage the data storage physical storage structure on parallel, multiple disk drives.
- At lowest level, stored in bits with physical addresses on secondary storage device.
- At highest level - viewed as files.
- Internal schema defines stored data types -uses a physical data model.
- Physical Data Independance
- Ability to change physical schema without affecting conceptual/external schema.
- Use different file organization, storage structures, storage devices, indexing strategy
- Mapping
Process of transforming request/response between various database levels of architecture.
- ENTITY
- Is an object we want to model/store information on, important to user/database.
- Noun: Can be concept (abstract or concrete),person, place, thing or event.
- ENTITY SET
- An entity set is a set of objects of the same type i.e. they have the same properties and behave in a similar way
- ATTRIBUTE
- Is a property of an object, the characteristics of an object (fields/columns)
- PRIMARY KEY
- An entity that is a unique identifier (key field)
- FOREIGN KEY
- An entity that references a primary key in another table.
- ENTERPRISE RULES
- The Enterprise Rules describe the rules of the business (found by interviewing) used to create entity relationships/database.
- Always start with “A”
- A student must enrol on exactly one course
- A course is enrolled on by zero, one or many
- ENTITY RELATIONSHIPS
- Relationship - association between entities (objects)
- Verb:
- Customer places an order
- Student enrols on a course.
-
-