to Approaches Schema Design need to identify basic components entity types, relationship types, attributes and for each of these components key attributes cardinality and participation constraints of relationships hierarchical relationships, were relevant weak entity types
this process can be done using two different approaches 1. centralized, or one shot schema design 2. view integration the difference is the stage in which the merging of the requirements takes place
Centralized Schema Design a single schema is designed from the complete set of requirements requirements of different applications and user groups are merged into a single set of requirements before schema design external schemas for user groups and application are developed from the single schema assumption is that single administrator is responsible for deciding how to merge the different (and possibly conflicting) requirements centralized design can be difficult with many user groups and applications, merging all of the requirements can be arduous and time consuming can be difficult to merge informal description
View Integration a schema (or view) is designed for each user group and application views are designed by user groups and application developers each view reflects a differing set of requirements individuals view are merged into the global conceptual schema need to specify correspondence among entity types, relationship types and attributes before starting integration individual views can be reconstructed as external schemas after view integration view integration can be difficult requires a good methodology and software support need to integrate conflicting views and verify the consistency of inter-schema correspondences
Centralised ER Schema Design top down strategy start with schema containing high-level abstractions and apply successive top-down refinements may start by specifying only a few high level entity types as the specification of attributes starts, we can split these into lower level entity types and relationships
Centralised ER Schema Design bottom up strategy starts with a schema containing basic abstractions then proceeds by combining and adding to these may start with attributes and group these into entities and relationships can add new relationships among entities as design progresses Special case: inside out strategy attention is focussed on a central set of concepts, that are most evident modelling spreads outwards, by considering new concepts in the vicinity of existing ones
Centralised ER Schema Design mixed strategy
... but everything starts from the analysis of the requirements which usually are in natural language, and not particularly clear...
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
We wish to create a database for a company that runs training courses courses courses. For each course course participant participant , participant, identified by a code, we want to store the national insurance number, surname, age, sex, place of birth, employer's name, address and telephone number , previous employers (and period employed), the course attended and the final assessment of each course . We need also to represent the seminars that each participant is attending at present and, for each day, the places and times the classes are held. Each course has a code and a title and any course can be given any number of times. Each time a course is given, we call it an "edition" of the course . For each edition, we represent the start and end dates and the number of participants . If a trainee is a self employed professional, we need to know his or her area of expertise, and, if appropriate, his or her title . For somebody who works for a company we store the level and position held. For each instructor we will show surname, age, place of birth, the edition the course is taught, those taught in the past and the courses the tutor is qualified to teach. All the instructor 's telephone numbers are also stored. An instructor can be permanently employed or can be freelance.
Natural Language Specification Problems there is an interchangeable use of names (synonyms problem): participants/trainees, tutors/instructors, courses/seminars some names are used with ambiguous meaning (homonyms problem): place means town (of birth) in line 3 and 15, and classroom in line 7 some terms are too general: period (line 5) can be specified better as start date and end date, assessment (line 5) is a mark (perhaps out of 10) title (line 13) is rather a professional title there are unnecessarily complex phrases: somebody who works for a company (line 13-14) is an employee some cross references are not explicit (may be ambiguous): are address and telephone number (line 4) referring to the trainees or their employers?
Rewrite Specification construct a glossary of terms, such as:
Term Trainee Instructor Course Company Description Participant in a course. Can be an employee or self-employed Corse tutor. Can be freelance Course offered. Can have various edition Company by which participant is or has been employed. Synonym Participant Tutor Seminar
rewrite specification using terms in the glossary rearrange specification, breaking down the text into groups of homogeneous phrases, referring to the same concept
Phrases of a general nature We wish to create a database for a company that runs training courses.
Phrases relating to trainees For each trainee, identified by a code, we will hold the national insurance number, surname, age, sex, town of birth, current employer, previous employers (along with start date and end date of employment), the editions of the courses the trainee is attending at present and those he or she has attended in the past, with the final mark, out of ten.
Phrases relating to the employers of the trainees For each employer of a trainee we will hold name, address and telephone number.
Phrases relating to specific types of trainees For a trainee who is a self-employed professional, we will hold the area of expertise and, if appropriate, the professional For title. a trainee who is an employee, we will hold the level and position held.
Phrases relating to the courses For each course we will hold the name and code. Each time a particular course is given, we will call it an "edition" of the course. For each edition, we will hold start date, end date and the number of participants. For the editions currently in progress, we will hold the dates, the classrooms and the times in which the classes are held.
Phrases relating to the instructors For each instructor we will hold surname, age, town of birth, all telephone numbers, the edition of courses taught, those taught in the past and the courses the instructor is qualified to teach. The instructors can be permanently employed or can be freelance.
General Criteria for Data Representation if a concept has significant properties and/or describes classes of objects with an autonomous existence, it is appropriate to represent it as an entity
for example, an instructor can be an entity, as it possesses various properties (name etc) and its existence does not depend from other concepts
General Criteria for Data Representation if a concept has a simple structure, and has no relevant properties associated with it, it is convenient to represent it as an attribute of another concept to which it refers for example: a town may well be an entity in general, but for this application it can more appropriately be modelled as an attribute
General Criteria for Data Representation if the requirements contain a concept that provides a logical link between two or more entities, this concept can be represented by a relationship for example, the concept of attending a course. some cases can be controversial: for example, a match can be thought as a relationship between two teams and a stadium, or as an entity in its own right
Top Down Strategy identify main entities and relationships Courses Trainees Instructors Trainees attend Courses Instructors teach Courses
construct high level schema identify cases in which an entity describes two different concepts logically linked to each other:
TRAINEE
ATTENDS
COURSE
TEACHES
INSTRUCTOR
from 1 entity to 2 entities+relationship identify cases in which an entity describes two different concepts logically linked to each other:
TRAINEE
ATTENDS
COURSE
TEACHES
INSTRUCTOR
TRAINEE
ATTENDS
EDITION
TEACHES
INSTRUCTOR
HELD IN
COURSE TYPE
from 1 entity to 1 entity+N entities+N relationships identify cases in which an entity is made up of distinct subentities
TRAINEE
ATTENDS
EDITION
TEACHES
INSTRUCTOR
HELD IN
COURSE TYPE
EMPLOYEE
IS A
TRAINEE
ATTENDS
EDITION
TEACHES
INSTRUCTOR
IS A
HELD IN
PROFESSIONAL COURSE TYPE
from 1 relationship to multiple relationships identify cases in which a relationship describes two or more different concepts linking the same entities
EMPLOYEE
IS A
TRAINEE
ATTENDS
EDITION
TEACHES
INSTRUCTOR
IS A
HELD IN
PROFESSIONAL COURSE TYPE
EMPLOYEE
IS A
ATTENDS EDITION
HAS ATTENDED
TEACHES INSTRUCTOR
TRAINEE
IS A
HELD IN
HAS TAUGHT
PROFESSIONAL COURSE TYPE
from 1 relationship to 1 entity + relationships identify cases in which a relationship describes a concept having an autonomous existence
EMPLOYEE
IS A
ATTENDS EDITION
HAS ATTENDED
TEACHES INSTRUCTOR
TRAINEE
IS A
HELD IN
HAS TAUGHT
PROFESSIONAL COURSE TYPE
EMPLOYEE
CLASSROOM
IS A
ATTENDS EDITION
HAS ATTENDED
TEACHES INSTRUCTOR
TRAINEE
IS A
HELD IN
HAS TAUGHT
PROFESSIONAL COURSE TYPE
repeat the above processes for any new entity and relationship introduced
EMPLOYEE
CLASSROOM
IS A
ATTENDS EDITION
HAS ATTENDED
TEACHES...