CLICK HERE TO DOWNLOAD PPT ON TRADITIONAL DATABASE APPLICATIONS
Traditional Database Applications Presentation Transcript
1.Introduction
Traditional database applications
New applications: multimedia databases,
geographic information system,
data warehouses, …
Database: A collection of related data.
Data: Known facts that can be recorded and have an implicit meaning.
(e.g., names, telephone numbers, addresses, …)
(indexed address book, diskette, …)
Traditional database applications
New applications: multimedia databases,
geographic information system,
data warehouses, …
Database: A collection of related data.
Data: Known facts that can be recorded and have an implicit meaning.
(e.g., names, telephone numbers, addresses, …)
(indexed address book, diskette, …)
2.Mini-world (Universe of Discourse): Some part of the real world about which data is stored in a database. For example, student grades and transcripts at a university.
Library card catalog: generated & maintained manually
Computerized database
Library card catalog: generated & maintained manually
Computerized database
3.Define UNIVERSITY database
Structure of the record
STUDENT ( Name , Number, Class, Major)
COURSE ( Name , Number, Credit, Dept.)
Data type of data element
Name: a string of characters
Number: integer
Grade: {A,B,C,D,F,I}
Constraints
The sections that students take must be taught by some instructors.
Structure of the record
STUDENT ( Name , Number, Class, Major)
COURSE ( Name , Number, Credit, Dept.)
Data type of data element
Name: a string of characters
Number: integer
Grade: {A,B,C,D,F,I}
Constraints
The sections that students take must be taught by some instructors.
4.Construct UNIVERSITY database
Store data on storage medium
store data for each student, course, section, grade repot, prerequisite
records in various files may be related to one another
Manipulate UNIVERSITY database
Query:
Retrieve the transcript ( a list of all courses and grades) of Smith.
Update:
Create a new section for the database course for this semester.
Store data on storage medium
store data for each student, course, section, grade repot, prerequisite
records in various files may be related to one another
Manipulate UNIVERSITY database
Query:
Retrieve the transcript ( a list of all courses and grades) of Smith.
Update:
Create a new section for the database course for this semester.
5.Figure 1.1 A simplified database system environment
6.1.2 An Example
Mini-world for the example: Part of a UNIVERSITY environment.
Some mini-world entities:
STUDENTs
COURSEs
SECTIONs (of COURSEs)
(academic) DEPARTMENTs
INSTRUCTORs
Some mini-world relationships:
SECTIONs are of specific COURSEs
STUDENTs take SECTIONs
COURSEs have prerequisite COURSEs
INSTRUCTORs teach SECTIONs
COURSEs are offered by DEPARTMENTs
STUDENTs major in DEPARTMENTs
Mini-world for the example: Part of a UNIVERSITY environment.
Some mini-world entities:
STUDENTs
COURSEs
SECTIONs (of COURSEs)
(academic) DEPARTMENTs
INSTRUCTORs
Some mini-world relationships:
SECTIONs are of specific COURSEs
STUDENTs take SECTIONs
COURSEs have prerequisite COURSEs
INSTRUCTORs teach SECTIONs
COURSEs are offered by DEPARTMENTs
STUDENTs major in DEPARTMENTs
7.Entity-Relationship Model
A logical representation of the data for an organization or for a business area
3 constructs:
Entity - person, place, object, event, concept (often corresponds to a row in a table)
Attribute - property or characteristic of an entity (often corresponds to a field in a table)
Relationship – link between entities (corresponds to primary key-foreign key equivalencies in related tables)
Type vs. instance
Entity type: Instructor
Entity instance: Leon Chen
Entity-Relationship Diagram
A graphical representation of entity-relationship model
Also called E-R diagram or just ERD
A logical representation of the data for an organization or for a business area
3 constructs:
Entity - person, place, object, event, concept (often corresponds to a row in a table)
Attribute - property or characteristic of an entity (often corresponds to a field in a table)
Relationship – link between entities (corresponds to primary key-foreign key equivalencies in related tables)
Type vs. instance
Entity type: Instructor
Entity instance: Leon Chen
Entity-Relationship Diagram
A graphical representation of entity-relationship model
Also called E-R diagram or just ERD
8.Sample E-R Diagram (Figure 3-1)
9.Entity
Person: EMPLOYEE, STUDENT
Place: WAREHOUSE, COUNTRY
Object: BUILDING, MACHINE
Event: SALE, REGISTRATION
Concept: ACCOUNT, COURSE
Person: EMPLOYEE, STUDENT
Place: WAREHOUSE, COUNTRY
Object: BUILDING, MACHINE
Event: SALE, REGISTRATION
Concept: ACCOUNT, COURSE
10.What Should an Entity Be?
SHOULD BE:
An object that will have many instances in the database
An object that will be composed of multiple attributes
An object that we are trying to model
SHOULD NOT BE:
A user of the database system
An output of the database system (e.g. a report)
SHOULD BE:
An object that will have many instances in the database
An object that will be composed of multiple attributes
An object that we are trying to model
SHOULD NOT BE:
A user of the database system
An output of the database system (e.g. a report)
11.Inappropriate entities
12.Attribute
Attribute - property or characteristic of an entity type
Classifications of attributes:
Required vs. Optional Attributes
Simple vs. Composite Attribute
Single-Valued vs. Multivalued Attribute
Stored vs. Derived Attributes
Identifier Attributes - keys
Attribute - property or characteristic of an entity type
Classifications of attributes:
Required vs. Optional Attributes
Simple vs. Composite Attribute
Single-Valued vs. Multivalued Attribute
Stored vs. Derived Attributes
Identifier Attributes - keys
13.Required vs. Optional Attributes
Example: entity – ONLINE_ACCOUNT
Required attributes (Not NULL)
Account_ID
Password
Owner_Name
Optional attributes
Phone_Number
Password_Hint
Example: entity – ONLINE_ACCOUNT
Required attributes (Not NULL)
Account_ID
Password
Owner_Name
Optional attributes
Phone_Number
Password_Hint
14.Figure 3-7 – A composite attribute
15.Figure 3-8 – Entity with a multivalued attribute (Skill) and derived attribute (Years_Employed)
16.Figure 3-19 – An attribute that is both multivalued and composite
17.Identifiers (Keys)
Identifier (Key) - An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type
Candidate Key – an attribute that could be a key. Examples (for STUDENT, PERSON)?
Simple Key versus Composite Key
Identifier (Key) - An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type
Candidate Key – an attribute that could be a key. Examples (for STUDENT, PERSON)?
Simple Key versus Composite Key
18.Figure 3-9a Simple key attribute
19.Figure 3-9b Composite key attribute
20.Guidelines for Identifiers
Will not change in value
Will not be null
Substitute new, simple keys for long, composite keys
Game_Number
21.Relationship
Relationship Type vs. Relationship Instance
Degree of a relationship
Cardinality of a relationship
Associative Entity – combination of relationship and entity
Will not change in value
Will not be null
Substitute new, simple keys for long, composite keys
Game_Number
21.Relationship
Relationship Type vs. Relationship Instance
Degree of a relationship
Cardinality of a relationship
Associative Entity – combination of relationship and entity
22.attribute of the relationship
23.Relationship Instance is between specific entity instances
24.Degree of a relationship is the number of entity types that participate in it
25.Cardinality of a Relationship
One-to-One
Each entity in the relationship will have exactly one related entity
One-to-Many
An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity
Many-to-Many
Entities on both sides of the relationship can have many related entities on the other side
One-to-One
Each entity in the relationship will have exactly one related entity
One-to-Many
An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity
Many-to-Many
Entities on both sides of the relationship can have many related entities on the other side
26.Cardinality Constraints
Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity
Minimum Cardinality. Examples?
If zero, then optional
If one or more, then mandatory
Maximum Cardinality. Examples?
The maximum number
Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity
Minimum Cardinality. Examples?
If zero, then optional
If one or more, then mandatory
Maximum Cardinality. Examples?
The maximum number
27.Cardinalities
28.Figure 3-16a Basic relationship
29.Figure 3-17c
Optional cardinalities with unary degree, one-to-one relationship
Optional cardinalities with unary degree, one-to-one relationship
30.Attributes or Relationship?
31.Attributes or Relationship?
32.Associative Entities
It’s an entity – it has attributes
AND it’s a relationship – it links entities together
When should a relationship with attributes be an associative entity?
All relationships for the associative entity should be many
The associative entity could have meaning independent of the other entities
The associative entity should have at least one or more attributes other than the identifier
The associative entity may participate in other relationships other than the entities of the associated relationship
Ternary relationships should be converted to associative entities
It’s an entity – it has attributes
AND it’s a relationship – it links entities together
When should a relationship with attributes be an associative entity?
All relationships for the associative entity should be many
The associative entity could have meaning independent of the other entities
The associative entity should have at least one or more attributes other than the identifier
The associative entity may participate in other relationships other than the entities of the associated relationship
Ternary relationships should be converted to associative entities
33.Figure 3-18 – Ternary relationship as an associative entity
34.Strong vs. Weak Entities
Strong entities
exist independently of other types of entities
has its own unique identifier
represented with single-line rectangle
Weak entity
dependent on a strong entity…cannot exist on its own
does not have a unique identifier
represented with double-line rectangle
Identifying relationship
links strong entities to weak entities
represented with double line diamond
Strong entities
exist independently of other types of entities
has its own unique identifier
represented with single-line rectangle
Weak entity
dependent on a strong entity…cannot exist on its own
does not have a unique identifier
represented with double-line rectangle
Identifying relationship
links strong entities to weak entities
represented with double line diamond
35.Library Case Study
When a library first receives a book from a publisher it is sent, together with the accompanying delivery note, to the library desk. Here the delivery note is checked against a file of books ordered.
If no order can be found to match the note, a letter of enquiry is sent to the publishers. If a matching order is found, a catalogue note is prepared from the details on the validated delivery note.
The catalogue note, together with the book, is sent to the registration department. The validated delivery note is sent to the accounts department where it is stored.
On receipt of an invoice from the publisher, the accounts department checks its store of delivery notes. If the corresponding delivery note is found then an instruction to pay the publishers is made, and subsequently a cheque is sent. If no corresponding delivery note is found, the invoice is stored in a pending file.
When a library first receives a book from a publisher it is sent, together with the accompanying delivery note, to the library desk. Here the delivery note is checked against a file of books ordered.
If no order can be found to match the note, a letter of enquiry is sent to the publishers. If a matching order is found, a catalogue note is prepared from the details on the validated delivery note.
The catalogue note, together with the book, is sent to the registration department. The validated delivery note is sent to the accounts department where it is stored.
On receipt of an invoice from the publisher, the accounts department checks its store of delivery notes. If the corresponding delivery note is found then an instruction to pay the publishers is made, and subsequently a cheque is sent. If no corresponding delivery note is found, the invoice is stored in a pending file.
36.A Case Study
Conference centre booking system
A conference centre takes bookings from clients who wish to hold courses or conferences at the
centre. When clients make bookings they specify how many people are included in the booking,
and of these, how many will be resident during the booking, and how many will require catered or
non-catered accommodation at the centre.
Conference centre booking system
A conference centre takes bookings from clients who wish to hold courses or conferences at the
centre. When clients make bookings they specify how many people are included in the booking,
and of these, how many will be resident during the booking, and how many will require catered or
non-catered accommodation at the centre.
The centre contains a number of facilities which may be required by clients making bookings as
follows:
A. There are 400 bedrooms for clients who will be resident during the Course or
conference.
B.A maximum of 250 catered people can be handled at any one time.
C.Six main lecture theatres providing seating for 200 people.
D.Twenty seminar rooms each able to accommodate 25 people.
E. Video conference facilities. The video conference facilities consist of four separate
video conference networks. Each video conference network has a large screen based
in one of the main lecture theatres, along with 3 satellite screens each of which is
based in one of the seminar rooms.
Draw an entity relationship diagram for the case, stating any assumptions you deem necessary.
follows:
A. There are 400 bedrooms for clients who will be resident during the Course or
conference.
B.A maximum of 250 catered people can be handled at any one time.
C.Six main lecture theatres providing seating for 200 people.
D.Twenty seminar rooms each able to accommodate 25 people.
E. Video conference facilities. The video conference facilities consist of four separate
video conference networks. Each video conference network has a large screen based
in one of the main lecture theatres, along with 3 satellite screens each of which is
based in one of the seminar rooms.
Draw an entity relationship diagram for the case, stating any assumptions you deem necessary.
37.Shipping company example
The London and Ireland Shipping Company PLC (LISC) was founded in 1852 and owns a fleet of cargo ships. The company had historically run passenger liners, but recent policy decisions involved the sale of all passenger-carrying vessels. The company currently has 14 vessels, including one oil tanker and one tugboat operating out of Liverpool. Most of the vessels are registered in Liberia for tax reasons.
The London and Ireland Shipping Company PLC (LISC) was founded in 1852 and owns a fleet of cargo ships. The company had historically run passenger liners, but recent policy decisions involved the sale of all passenger-carrying vessels. The company currently has 14 vessels, including one oil tanker and one tugboat operating out of Liverpool. Most of the vessels are registered in Liberia for tax reasons.
Each ship has one or more holds divided into spaces. The holds are defined by steel bulkheads and the spaces are defined by shelf racks or other physical dividers. Sister ships, built by the same shipbuilders and to the same designs have similar names, such as Pride of Ireland, Queen of Ireland, Song of Ireland and Warrior of Ireland. Sister ships also have identical cargo storage facilities.
LISC issues contracts to agents for one or more manifests (lists of cargo items to be shipped). LISC's charges for cargo carried are based on the number of spaces the cargo requires for storage. The types of cargo typically carried by LISC include grain, coal and ores (carried only in ships equipped with bulk cargo holds). They also transport sacked grain, heavy cases, containers (which may be carried on deck), pallets and so on.
LISC issues contracts to agents for one or more manifests (lists of cargo items to be shipped). LISC's charges for cargo carried are based on the number of spaces the cargo requires for storage. The types of cargo typically carried by LISC include grain, coal and ores (carried only in ships equipped with bulk cargo holds). They also transport sacked grain, heavy cases, containers (which may be carried on deck), pallets and so on.
Cargo items may take up less than one space in a hold, or one or more spaces, depending on the size of the item. A space may therefore contain several small cargo items.
The ships owned by LISC are kept as busy and as full as possible, in order to maximise the profits that each vessel makes and minimise running & operating costs. LISC's ships ply most of the seas of the world, but tend to operate mainly in the Mediterranean, the North and Mid Atlantic and the Indian Ocean. Different ships require different crew complements.
LISC intends to create a computer based information system that will be able to perform the following tasks:
• record the voyages of each ship with the start and end ports.
• record the cargo held by a ship on each voyage
• keep records of their employees and the ships they are assigned to
• producing invoices for agents and customers
• keep a record of customers' payments on invoices
• analyse the efficiency of use of cargo space and of percentage wasted cargo space for ships voyages
The ships owned by LISC are kept as busy and as full as possible, in order to maximise the profits that each vessel makes and minimise running & operating costs. LISC's ships ply most of the seas of the world, but tend to operate mainly in the Mediterranean, the North and Mid Atlantic and the Indian Ocean. Different ships require different crew complements.
LISC intends to create a computer based information system that will be able to perform the following tasks:
• record the voyages of each ship with the start and end ports.
• record the cargo held by a ship on each voyage
• keep records of their employees and the ships they are assigned to
• producing invoices for agents and customers
• keep a record of customers' payments on invoices
• analyse the efficiency of use of cargo space and of percentage wasted cargo space for ships voyages
38.1.3 Characteristics of the Database Approach
File Processing
Each user defines and implements the files needed for a specific application
Redundancy in defining & storing data
Database Approach
A single repository of data
Self-describing nature of a database system: A DBMS catalog stores the description of the database. The description is called meta-data . This allows the DBMS software to work with different databases. catalog: structure of each file, type & storage format of each data item, constraints on data
Insulation between programs and data: Called program-data independence.
Allows changing data storage structures without having to change the DBMS access programs.(see 1-7)
File Processing
Each user defines and implements the files needed for a specific application
Redundancy in defining & storing data
Database Approach
A single repository of data
Self-describing nature of a database system: A DBMS catalog stores the description of the database. The description is called meta-data . This allows the DBMS software to work with different databases. catalog: structure of each file, type & storage format of each data item, constraints on data
Insulation between programs and data: Called program-data independence.
Allows changing data storage structures without having to change the DBMS access programs.(see 1-7)
39.1.6 Advantages of Using a DBMs
1.6.1 Controlling Redundancy in data storage and in development and maintenance efforts. duplication efforts ?waste space ?inconsistent ( see 1-12 controlled redundancy) 1.6.2 Restricting Unauthorized Access (security and authorization)
1.6.3 Providing Persistent Storage for Program Objects and Data Structures.
1.6.4 Permitting Inferencing and Actions Using Rules
1.6.5 Providing Multiple User Interfaces
1.6.6 Representing Complex Relationships Among data.
1.6.7 Enforcing Integrity Constraints
1.6.8 Providing Backup and Recovery
40.1.7 Implications of the Database Approach
Potential for Enforcing Standards.
Reduced Application Development Time.
Flexibility.
Availability of Up-to-date Information.
Economies of Scale.
41.1.8 When not to use a DBMS
Main costs of using a DBMS:
High initial investment in hardware, software,training
and possible need for additional hardware.
Overhead for providing generality, security, recovery, integrity, and concurrency control.
Generality that a DBMS provides for defining and processing data.
When a DBMS may be unnecessary:
If the database and applications are simple, well defined, and not expected to change.
If there are stringent real-time requirements that may not be met because of DBMS overhead.
If access to data by multiple users is not required.
1.6.1 Controlling Redundancy in data storage and in development and maintenance efforts. duplication efforts ?waste space ?inconsistent ( see 1-12 controlled redundancy) 1.6.2 Restricting Unauthorized Access (security and authorization)
1.6.3 Providing Persistent Storage for Program Objects and Data Structures.
1.6.4 Permitting Inferencing and Actions Using Rules
1.6.5 Providing Multiple User Interfaces
1.6.6 Representing Complex Relationships Among data.
1.6.7 Enforcing Integrity Constraints
1.6.8 Providing Backup and Recovery
40.1.7 Implications of the Database Approach
Potential for Enforcing Standards.
Reduced Application Development Time.
Flexibility.
Availability of Up-to-date Information.
Economies of Scale.
41.1.8 When not to use a DBMS
Main costs of using a DBMS:
High initial investment in hardware, software,training
and possible need for additional hardware.
Overhead for providing generality, security, recovery, integrity, and concurrency control.
Generality that a DBMS provides for defining and processing data.
When a DBMS may be unnecessary:
If the database and applications are simple, well defined, and not expected to change.
If there are stringent real-time requirements that may not be met because of DBMS overhead.
If access to data by multiple users is not required.
42.Summary
In today’s session we have learned to:
Identify the entities
Determine the attributes for each entity
Select the primary key for each entity
Establish the relationships between the entities
Draw an entity model
In today’s session we have learned to:
Identify the entities
Determine the attributes for each entity
Select the primary key for each entity
Establish the relationships between the entities
Draw an entity model
Posts
0 comments