cois20026 lecture data model basic week 2

8/6/2019 COIS20026 Lecture Data Model Basic Week 2

1/64

COIS20026COIS20026

Database Development &Database Development &ManagementManagement

Module 2Module 2 ConceptualConceptual modellingmodelling::Basic structuresBasic structures


2/64

ObjectivesObjectives

Describe and illustrate the ANSI/SPARCDescribe and illustrate the ANSI/SPARCarchitecture, and relate the ER model to thisarchitecture, and relate the ER model to this

Understand the ER model as a conceptualUnderstand the ER model as a conceptualmodelling technique and develop ER modelsmodelling technique and develop ER modelsusing the notation given in the set textusing the notation given in the set text

Describe the role of business rules in anDescribe the role of business rules in an

organisationorganisation

Be aware of the flow, structure and stateBe aware of the flow, structure and stateaspects of dataaspects of data

22


3/64

33

ObjectivesObjectives

Define & describe the difference between:Define & describe the difference between:

Entity types and entity instancesEntity types and entity instances

Relationship types and relationship instancesRelationship types and relationship instances

Attributes and valuesAttributes and values

Define and appropriately select:Define and appropriately select:

maximum & minimum cardinalitymaximum & minimum cardinality

Unique identifierUnique identifier

Note: concepts & definitions in these slides are derived from and complement the set text


4/64

44

ANSI/SPARC threeANSI/SPARC three--schemaschema

architecture for DB developmentarchitecture for DB development Recall the problem discussed last week ofRecall the problem discussed last week of

filefile--based systemsbased systems

Programs must know the location andPrograms must know the location andstructure of the data files in a FBSstructure of the data files in a FBS

Databases changed thatDatabases changed that the DBMS hidthe DBMS hid

the physical location/structure of the datathe physical location/structure of the data Thats because databases generally followThats because databases generally follow

the ANSI/SPARC architecturethe ANSI/SPARC architecture


5/64

55

ANSI/SPARC threeANSI/SPARC three--schemaschema

architecture for DB developmentarchitecture for DB development

In 1978 the ANSI/SPARC committeeIn 1978 the ANSI/SPARC committeepublished a document describing thepublished a document describing thethreethree--schema architectureschema architecture (or three(or three--level)level)

Also known as theAlso known as theANSI/SPARCANSI/SPARC

architecturearchitecture

Describes important separation of levelsDescribes important separation of levels


6/64

66

Three schema architectureThree schema architecture


7/64

77

External schemaExternal schema

Consists of user viewsConsists of user views

Each user view is a logical description ofEach user view is a logical description ofsome portion of data required by users tosome portion of data required by users toperform a taskperform a task

Logical description means that it doesntLogical description means that it doesnt

involve physical storage informationinvolve physical storage information

Also called externalAlso called external levellevel


8/64

88

Conceptual schemaConceptual schema

Detailed logical specification of all data inDetailed logical specification of all data inthe organisation/databasethe organisation/database

Each user view would be a subset of theEach user view would be a subset of theconceptual schemaconceptual schema

Independent of any storage technologyIndependent of any storage technology

Also called conceptualAlso called conceptual levellevel


9/64

99

Internal schemaInternal schema

Consists of Logical schema and PhysicalConsists of Logical schema and Physicalschemaschema

Logical schema = representation of dataLogical schema = representation of datafor a specific DBMS (eg for a relational DBfor a specific DBMS (eg for a relational DBas opposed to an OODB)as opposed to an OODB)

Physical schema = how data isPhysical schema = how data isrepresented/stored in physical storagerepresented/stored in physical storage

Also called internalAlso called internal levellevel


10/64

1010

Conceptual modellingConceptual modelling

To design a database, we must firstTo design a database, we must firstdetermine whatdetermine what datadata must be recorded formust be recorded forthe business in questionthe business in question

Conceptual modelling is the process ofConceptual modelling is the process ofconceiving, abstracting, naming &conceiving, abstracting, naming &representing (usually as a diagram) the datarepresenting (usually as a diagram) the data

that must be recordedthat must be recorded Its not simple; expect to spend a lot of timeIts not simple; expect to spend a lot of time

investigating and analysing before you caninvestigating and analysing before you canconstruct a conceptual model.construct a conceptual model.


11/64

Overview of database analysis,Overview of database analysis,

design & development processdesign & development process

1111

Examine existing processes(what data needs to be

recorded?)

Developconceptual

model

Implementdatabase

Check DB designfor normalisation

Do physicaldesign

Convert modelto logical

design

Develop applicationor web-based interface

(will involve SQL)

Test andmaintain...


12/64

1212

Conceptual modellingConceptual modelling

There are many different forms ofThere are many different forms ofconceptual modelling:conceptual modelling:

EntityEntity--relationship modellingrelationship modelling very common,very common,we will focus on this onewe will focus on this one

UMLUML becoming more common, we will givebecoming more common, we will giveyou a brief overview so that youre aware of ityou a brief overview so that youre aware of it

Other forms such as conceptual schemaOther forms such as conceptual schemadiagrams and semantic object modellingdiagrams and semantic object modelling rarely used, we wont look at these.rarely used, we wont look at these.


13/64

ER modelling syntaxER modelling syntax

The symbols used in an ER modelling areThe symbols used in an ER modelling arenot yet standardised sonot yet standardised so --

diagrams may appear somewhat differentdiagrams may appear somewhat differentdepending upon who created themdepending upon who created them

The different ER syntaxes are similar, so youThe different ER syntaxes are similar, so you

should be able to switch from one ER syntaxshould be able to switch from one ER syntaxto another once youre familiar withto another once youre familiar with

Well follow the format shown in the set textWell follow the format shown in the set text


14/64

1414

ER modellingER modelling EntityEntity--relationship modelling (relationship modelling (ER modellingER modelling

for short) is sofor short) is so--called because it modelscalled because it modelsentitiesentities andand relationshipsrelationships (and attributes).(and attributes).

For example,For example, CQUniCQUni needs to record details of:needs to record details of: Each student (anEach student (an entityentity))

Each course (anotherEach course (another entityentity))

Which students have enrolled in which courses (aWhich students have enrolled in which courses (arelationshiprelationship between students and courses)between students and courses)

For each student, we would record such things asFor each student, we would record such things asyour ID, name, address, etc (these areyour ID, name, address, etc (these are attributesattributesof students)of students)


15/64

A basic ER exampleA basic ER example

So if we were to draw an ER diagram forSo if we were to draw an ER diagram forour students & courses entities andour students & courses entities and

enrols in relationship atenrols in relationship at CQUniCQUni, it would, it wouldlook like this:look like this:

1515


16/64

Basic syntaxBasic syntax

1616


17/64

What does this model mean?What does this model mean?

This ER model depictsThis ER model depicts the data that isthe data that isrecorded by CQUrecorded by CQU##, specifically:, specifically:

CQU keeps a list of all its studentsCQU keeps a list of all its students

CQU keeps a list of all its coursesCQU keeps a list of all its courses CQU recordsCQU records which students enrol in whichwhich students enrol in which

courses.courses.

1717


18/64

1818

ModellingModelling data structuredata structure

Notice in the previous example that we haveNotice in the previous example that we havebeen only concerned withbeen only concerned with datadata that must bethat must berecordedrecorded.. ##

We doWe do notnot model:model: Business processes or functions (Monthly invoiceBusiness processes or functions (Monthly invoice

statements are issued on the last day of the month)statements are issued on the last day of the month)

Data flow (Order detailsData flow (Order details are sent toare sent to the dispatchthe dispatch

office)office) Forms or reportsForms or reports

Individuals who might be responsible for certain tasksIndividuals who might be responsible for certain tasks


19/64

1919

ModellingModelling data structuredata structure These things are useful because they tell us aboutThese things are useful because they tell us about

the data that is needed, but we do not model themthe data that is needed, but we do not model themas part of database development.as part of database development.

For example:For example: Monthly invoice statements are issued on the last day ofMonthly invoice statements are issued on the last day of

the monththe month tells us that we almost certainly need totells us that we almost certainly need tokeep a list of invoiceskeep a list of invoices

Order detailsOrder details are sent toare sent to the dispatch officethe dispatch office tells ustells us

that we need to keep a list of Orders and possibly alsothat we need to keep a list of Orders and possibly alsodepartments/officesdepartments/offices

Forms/ reportsForms/ reports tell us what data items the businesstell us what data items the businessrecordsrecords


20/64

2020

Data structureData structure vsvs flowflow vsvs statestate ER diagrams modelER diagrams model data structuredata structure the staticthe static

structure of data items and the relationshipsstructure of data items and the relationshipsbetween thembetween them

Data flow diagrams (not covered in this course)Data flow diagrams (not covered in this course)modelmodel data flowdata flow it shows what data moves fromit shows what data moves fromone process to anotherone process to another

State change diagrams (not covered in this course)State change diagrams (not covered in this course)modelmodel statestate changeschanges it shows possible events andit shows possible events andthe change in state that the events causethe change in state that the events cause

There are other ways to slice & dice data but weThere are other ways to slice & dice data but wemodel the data structure only!model the data structure only!


21/64

EntitiesEntities

So what exactly is an entity?So what exactly is an entity?

AnAn entityentity is a person, place, object, eventis a person, place, object, event

or concept about which the organisationor concept about which the organisationwishes to maintain data.wishes to maintain data. (McFadden, et al, 1999, p 89)(McFadden, et al, 1999, p 89)


22/64

Example entitiesExample entities

PersonPerson: Employee, Student, Customer,: Employee, Student, Customer,SupplierSupplier

PlacePlace: City, Province, State, Territory,: City, Province, State, Territory,LocationLocation

ObjectObject: Vehicle, Building, Airplane, Part,: Vehicle, Building, Airplane, Part,AnimalAnimal

EventEvent: Purchase, Sale, Registration, Game: Purchase, Sale, Registration, Game ConceptConcept: Account, Course, Work, Centre,: Account, Course, Work, Centre,

DepartmentDepartment


23/64

Entity instanceEntity instance vsvs Entity typeEntity type

Note the difference between anNote the difference between an EntityEntitytypetype and anand an EntityEntity instanceinstance..

StudentStudent is an entityis an entity typetype; it describes; it describesstudentsstudents in generalin general

A single student is anA single student is an instanceinstance of studentof student

CourseCourse is an entity typeis an entity type COIS20026 is an instance of a courseCOIS20026 is an instance of a course

COIS20024 is an instance of a courseCOIS20024 is an instance of a course

2323


24/64

Examples of Course instancesExamples of Course instances

CourseIDCourseID: COIS20026: COIS20026

CourseNameCourseName: Database Development & Mgt: Database Development & Mgt

Credit points: 8Credit points: 8

CourseIDCourseID: COIS20024: COIS20024

CourseNameCourseName: Systems Development Overview: Systems Development Overview Credit points: 8Credit points: 8


25/64

AttributesAttributes vsvs valuesvalues

Note that entity types have attributesNote that entity types have attributes

Entity instances have valuesEntity instances have values

For example, these are valuesFor example, these are values These are attributesThese are attributes

CourseIDCourseID: COIS20026: COIS20026CourseNameCourseName: Database Development & Mgt: Database Development & Mgt

Credit points: 8Credit points: 8


26/64

Entity instanceEntity instance vsvs Entity typeEntity type

Note that we only model EntityNote that we only model Entity TypesTypes;;entity instances have no place in ourentity instances have no place in our

modelsmodels

When we say entity, we almost alwaysWhen we say entity, we almost alwaysmean entitymean entity typetype..

2626


27/64

Finding EntitiesFinding Entities Entities are normally identified throughEntities are normally identified through

user interviews, examining existinguser interviews, examining existingprocesses, looking at forms, etc.processes, looking at forms, etc.

Search for entities by:Search for entities by: looking for nouns (but beware not all nounslooking for nouns (but beware not all nouns

become entities)become entities)

finding objects/things that have manyfinding objects/things that have many

instancesinstances have descriptive/defining propertieshave descriptive/defining properties

(attributes)(attributes)

Instances are uniquely identifiableInstances are uniquely identifiable


28/64

Finding EntitiesFinding EntitiesAnother good technique for novices is toAnother good technique for novices is to

think: Whatthink: What thingsthings would I need towould I need to keepkeepa list ofa list of if I were running this business?if I were running this business?

For e.g., CQUFor e.g., CQU needs to keep lists ofneeds to keep lists of:: StudentsStudents

CoursesCourses

StaffStaff

EtcEtc

Each of these would become an entity.Each of these would become an entity.


29/64

AttributesAttributes For each entity, what needs to beFor each entity, what needs to be

recorded?recorded?

For example, for each student, we recordFor example, for each student, we recordStudentIDStudentID, Name, Address, phone, Name, Address, phonenumber, email etc.number, email etc.

These areThese are attributesattributes..

Attributes can beAttributes can be namingnaming (e.g., names of(e.g., names ofpeople) orpeople) or characterisingcharacterising (e.g., height and(e.g., height andcolour of hair).colour of hair).

2929


30/64

Syntax for attributesSyntax for attributes

3030

Our students & courses example wouldOur students & courses example wouldlook like this:look like this:


31/64


32/64

Identifier attributeIdentifier attribute

STUDENT

StudentID

Name

DateOfBirth

Address

ContactPhone

Email

This means thatnotwo students

may have thesame StudentID.Every student musthave a StudentID

and it must beunique.


33/64

Identifier AttributeIdentifier Attribute

persons name or phone number wouldpersons name or phone number wouldnot be suitablenot be suitable

can be simple key attribute; egcan be simple key attribute; egStudentIDStudentID

can be composite key attribute; egcan be composite key attribute; eg

FlightIDFlightID, where this is composed of, where this is composed of --FlightNumberFlightNumber + Date+ Date


34/64

RelationshipsRelationships

What does a relationshipWhat does a relationship mean?mean?

AA relationshiprelationship is a meaningful associationis a meaningful associationbetween one or more entity instancesbetween one or more entity instances


35/64

RelationshipsRelationships

Example:Example: TheThe enrols inenrols in relationship meansrelationship meansthatthat it isit is neccesaryneccesary for CQU to recordfor CQU to record

which student enrols in which coursewhich student enrols in which course Dont create a relationship unless theDont create a relationship unless the

business needs to know which courses abusiness needs to know which courses astudent has enrolled in (or whatever)student has enrolled in (or whatever)


36/64

Relationship InstanceRelationship Instance

Just as there are entity types and entityJust as there are entity types and entityinstances, there are relationship types andinstances, there are relationship types andrelationship instancesrelationship instances

Fred Smith enrolled in COIS20026 is aFred Smith enrolled in COIS20026 is arelationship instancerelationship instance

Students enrol in courses is aStudents enrol in courses is arelationship type.relationship type.

We only model relationship typesWe only model relationship types


37/64

CardinalityCardinality

We have not explained this notation yetWe have not explained this notation yet

These crows feet represent theThese crows feet represent the

cardinalitycardinality of the relationshipof the relationship They tell us more about the relationshipThey tell us more about the relationship

3737


38/64

How to read/determineHow to read/determine

cardinalitycardinality

This one means that every student may enrolThis one means that every student may enrolin many courses, but may enrol in nonein many courses, but may enrol in none

The crows foot is the manyThe crows foot is the many The circle is the noneThe circle is the none

3838


39/64

How to read/determineHow to read/determine

cardinalitycardinality

This one means that every course may haveThis one means that every course may havemany enrolled students, and may have nonemany enrolled students, and may have none

The crows foot is the manyThe crows foot is the many The circle is the noneThe circle is the none

3939


40/64

Another exampleAnother example

This ER diagram represents (a portion of) theThis ER diagram represents (a portion of) thedata that might be required to run adata that might be required to run acustomercustomer--order business, such as a websiteorder business, such as a websitefor ordering parts onlinefor ordering parts online

4040


41/64


42/64

Another exampleAnother example

This one means that each order must haveThis one means that each order must havebeen placed bybeen placed by one and only oneone and only one customer.customer.

4242


43/64

Minimum cardinalityMinimum cardinality

The inner marks indicate minimum cardinalityThe inner marks indicate minimum cardinalityand may be either 0 (a circle) or 1 (a dash).and may be either 0 (a circle) or 1 (a dash).

Minimum cardinalityMinimum cardinality defines whether everydefines whether everyinstance of an entity typeinstance of an entity type mustmust participate inparticipate ina relationship (one) or the participation isa relationship (one) or the participation isoptionaloptional (zero).(zero).

4343


44/64

Maximum cardinalityMaximum cardinality

The outer marks indicate maximumThe outer marks indicate maximumcardinality and may be either 1 (a dash) orcardinality and may be either 1 (a dash) ormany (the crows foot).many (the crows foot).

Maximum cardinalityMaximum cardinality defines if an instancedefines if an instanceparticipates in a relationship with more thanparticipates in a relationship with more thanone instances of the other entity typeone instances of the other entity type

4444


45/64

Determining cardinalityDetermining cardinality

CUSTOMER ORDER

4545

Be careful how you read/determine cardinalities!

To determine the cardinality on the right, say:A single customer may have one or many orders.

To determine the cardinality on the left, say:

A single order may belong to one and only customer.

Customers place many orders and orders are placed bymany customers IS WRONG!


46/64

Cardinality of a relationshipCardinality of a relationship

We usually describe the cardinality of aWe usually describe the cardinality of arelationship by the maximum cardinalityrelationship by the maximum cardinality

4646


47/64

Importance of cardinalityImportance of cardinality

Cardinality is important! Particularly theCardinality is important! Particularly themaximum cardinalitymaximum cardinality

Make sure you practice reading andMake sure you practice reading anddetermining cardinalitydetermining cardinality

Even a seemingly small oversight inEven a seemingly small oversight incardinality can result in a serious error incardinality can result in a serious error in

the final database.the final database.

You must be very careful aboutYou must be very careful aboutcardinalities!cardinalities!


48/64

A larger exampleA larger example

4848

Data modelsData modelscan get quitecan get quite

big!big! You can keepYou can keep

adding entitiesadding entities& relationships& relationships


49/64

Try one for yourselfTry one for yourself

Assume you have been asked to create aAssume you have been asked to create aconceptual model for CQU.conceptual model for CQU.

CQU needs to keep lists of students,CQU needs to keep lists of students,courses, faculties and staff.courses, faculties and staff.

CQU needs to know:CQU needs to know:

Which students enrolled in which coursesWhich students enrolled in which courses

Which courses are owned by which facultiesWhich courses are owned by which faculties

Which staff teach which coursesWhich staff teach which courses

Which staff belong to which facultiesWhich staff belong to which faculties

4949


50/64


51/64

Try another one for yourselfTry another one for yourself

Assume youve been asked to create aAssume youve been asked to create aconceptual model for a doctors surgeryconceptual model for a doctors surgery

WhatWhat thingsthings will theywill they need to keep listsneed to keep listsofof??

What relationships are required?What relationships are required?

For eg, for each appointment they will needFor eg, for each appointment they will needto know which patient made the bookingto know which patient made the booking

Do they need to know which staff memberDo they need to know which staff membertook the booking over the phone?took the booking over the phone?

5151


52/64

A little problemA little problem

This means that everyThis means that everystudent has an ID,student has an ID,name, DOB, Address,name, DOB, Address,ContactPhoneContactPhone andandEmail.Email.

You can only record (atYou can only record (at

most)most) oneone of each.of each.

5252

STUDENT

StudentID

Name

DateOfBirth

Address

ContactPhone

Email


53/64


What aboutWhat about gradegrade??

As a student, you getAs a student, you get

one gradeone grade for eachfor eachcoursecourse..

If we model it like this,If we model it like this,then wed only be ablethen wed only be able

to record one grade forto record one grade foryou.you.

Grade cannot go here!Grade cannot go here!

5353

STUDENT

StudentID

Name

DateOfBirth

Address

ContactPhone

EmailGrade

WRONG!


54/64


This wont work eitherThis wont work either

This means you can record justThis means you can record justone grade for every course,one grade for every course,

even if it has 500 students!even if it has 500 students!

Grade cannot go here either!Grade cannot go here either!

Its not one grade per student,Its not one grade per student,

its not one grade per course,its not one grade per course,itsits one grade per student andone grade per student andcourse combination!course combination!

5454

COURSE

CourseID

CourseName

CreditPoints

Grade

WRONG!


55/64

Associative entitiesAssociative entities The answer is to create anThe answer is to create an associativeassociative

entityentity in between Course and Student.in between Course and Student.

5555


56/64

Associative EntitiesAssociative Entities

The unique identifier for each enrolmentThe unique identifier for each enrolmentwill be (will be (StudentIDStudentID,, CourseIDCourseID)) notnotshownshown

Associative entities can be used to replaceAssociative entities can be used to replaceM:N relationshipsM:N relationships onlyonly. You cannot. You cannotreplace 1:1 or 1:M withreplace 1:1 or 1:M with associativesassociatives..


57/64

Associative Entity ConditionsAssociative Entity Conditions

ReplaceReplace a M:Na M:N with an associative entity when:with an associative entity when:

all relationships for participating entities areall relationships for participating entities aremanymany

resulting associative entity has independentresulting associative entity has independentmeaning to end usersmeaning to end users

has 1 or more attributes in addition to identifierhas 1 or more attributes in addition to identifier

participates in 1 or more relationshipsparticipates in 1 or more relationshipsindependent of entities related in associatedindependent of entities related in associatedrelationshiprelationship


58/64

ER model and ANSI/SPARCER model and ANSI/SPARC

Notice how ER models represent dataNotice how ER models represent datawithout describing how/where that data iswithout describing how/where that data is

to be stored?to be stored? This means that ER modelling can be usedThis means that ER modelling can be used

to represent data at the conceptual orto represent data at the conceptual or

external schema levels of the ANSI/SPARCexternal schema levels of the ANSI/SPARCarchitecture.architecture.

5858


59/64

Business rulesBusiness rules

AA business rulebusiness rule is a statement that defines oris a statement that defines orconstrains some aspect of the business.constrains some aspect of the business.

When we do conceptual modelling, we areWhen we do conceptual modelling, we are

actually modellingactually modelling business rules that governbusiness rules that governdatadata along with the actualalong with the actual data that needs todata that needs tobe recordedbe recorded

Business rules will indicate:Business rules will indicate: entities, relationships, attributes to be recordedentities, relationships, attributes to be recorded

appropriate names for entities, etcappropriate names for entities, etc

constraints on entities, relationships etcconstraints on entities, relationships etc


60/64


For example, if CQU had a business ruleFor example, if CQU had a business rulestating that students werestating that students were requiredrequired to enrolto enrolin at least one course, then our model wouldin at least one course, then our model would

be:be:


61/64


Not all business rules affect the dataNot all business rules affect the datamodelmodel

Every student must enrol in at least oneEvery student must enrol in at least onecoursecourse this affects the modelthis affects the model

Invoices can only be paid by in person atInvoices can only be paid by in person atthe finance department at your campusthe finance department at your campus

this does not affect the model.this does not affect the model.

(Note: neither of these is a rule at(Note: neither of these is a rule at CQUni)CQUni)


62/64

Business RuleBusiness Rule -- A DefinitionA Definition

McFadden, et al (2005) define a businessMcFadden, et al (2005) define a businessrule as:rule as:

a statement that defines or constrains somea statement that defines or constrains someaspect of the business. It is intended to assertaspect of the business. It is intended to assertbusiness structure or to control or influencebusiness structure or to control or influencethe behaviour of the business (Guide tothe behaviour of the business (Guide to

Business Rules Project, 1997).Business Rules Project, 1997).


63/64

6363

Business RulesBusiness Rules some examplessome examples

A student may register for a course only ifA student may register for a course only ifhe or she has successfully completed thehe or she has successfully completed the

prerequisites for that course.prerequisites for that course.A preferred customer qualifies for a 10A preferred customer qualifies for a 10

percent discount, unless he or she has anpercent discount, unless he or she has an

overdue account balance.overdue account balance.


64/64

SummarySummary

Today we have looked at the basic constructs ofToday we have looked at the basic constructs ofthe Entitythe Entity--Relationship (ERelationship (E--R) model,R) model,

These constructs should handle the majority ofThese constructs should handle the majority ofthe modelling you will need to do.the modelling you will need to do.

You cant develop modelling skills by reading orYou cant develop modelling skills by reading orlistening. The most important thing you can do islistening. The most important thing you can do is

toto practise, practise, practisepractise, practise, practise andand practise somepractise somemore.more.

cois20026 lecture data model basic week 2

Documents