Intro & Data Modelling 1 + 2

What is a database?

Shared collection of logically, related data + its description (designed to meet needs of organisation)

What is a DBMS used for?

Database Management System

• software system enabling users to:

  • define

  • create

  • maintain

  • access control

database

What does a DBMS maintain?

data independent from application programs

What does a DBMS minimise?

data redundancy

What does a DBMS provide?

• data security

• data consistency

• data integrity

• shared access

• recovery control

What are the 4 stages of database development?

1. Problem analysis - identify + understand requirements

1. Database design - produce data models

2. Database implementation - structure data in physical databases

3. Database monitoring/tuning - monitor usage + optimise database

What are the 3 phases of database design?

What is data modelling?

understanding the problem domain

(identify, classify and structure elements in the design)

What do you have to identify when trying to understand the problem domain?

• diff. types of data objects (relevant to info. requirements)

• relationships among these objects

What is the aim in creating generic abstraction within data modelling?

aim to represent data requirements in a way that is easily understandable to a variety of people

What is ER Modelling?

Entity-Relationship modelling

• top down approach

• identifies entities + relationships

• stores attributes + constraints about entities

What are the 3 basic concepts of ER Modelling?

• entity

• attribute

• relationship

What is an entity?

group of objects of interest w/ same characteristics (e.g. concrete = film, car / abstract = registration, booking)

What is an instance of an entity?

a uniquely identifiable object of the entity

What does an attribute refer to?

properties of an entity/relationship

What is an attribute domain?

a set of allowable values for an attribute (e.g. quantity 1-10, DOB fits range, name is character string)

What are the 3 attribute types?

• single-valued

  • single value for each entity instane

• multi-valued

  • may contain more than one for an entity instance

• derived

  • may be derived from other attributes (e.g. age from DOB)

What is a candidate key?

min. set of attributes that uniquely identify an entity instance

(entity can have multiple)

What is a composite key?

a candidate key that consists of more than one attribute

What is a primary key?

candidate key selected to uniquely identify each entity instance

What are the 2 main requirements for a primary key?

• values must be unique

• cannot contain null value

What are the 2 things you can do when there is no single column that can be used as a primary key?

1. add a new column (e.g. auto-increment number)

2. combine multiple columns - composite PK

What are the 6 primary key guidelines?

1. unique value

2. no null

3. shouldn’t change over time

4. preferably single attribute

5. preferably numeric

6. security complaint

What is multiplicity?

defines numerical range of how many entity instances can be linked associated with another entity instance

What does multiplicity help us to define?

constraints on entity relationships

What are multiplicity constraints determined by?

the business rules of the organisation

What are the 3 types of abstraction that are the building blocks for all data models?

1. classification

2. aggregation

3. specialisation/generalisation

What does specialisation/generalisation define?

a hierarchical class (superclass) for a collection of subgroup classes (subclasses)

What type of attributes does the superclass contain?

common attributes - these inherited by subclasses

What type of relationship associates a subclass w/ a superclass?

is-a relationship

(e.g. current account is-a bank account, savings account is-a bank account)

What are 2 important benefits of specialisation/generalisation?

• avoids unnecessary nulls (when subclass has specialised attributes)

• enables specific subclass to participate in a relationship unique to that subclass

What is a limitation of the ER model in terms of specialisation/generalisation?

hierarchical relationships can’t be directly implemented in a relational database

What type of DBMS can hierarchical relationships be implemented in?

Object-Relational Database Management Systems (ORDBMS)

How can hierarchical relationships be modelled?

using enhanced entity relationship (EER) modelling

What are 2 important coverage properties that are a criteria for specialisation/generalisation abstraction?

• participant constraint

• disjoint/overlapping constraint

What are the 2 types of participant constraints?

• Mandatory - total participation

  • each instance of superclass MUST also be member of subclass

• Optional - partial participation

  • each instance of superclass DOES NOT need to be a member of a subclass

What is a disjoint constraint?

a superclass instance can only be a member one subclass (OR)

What is a overlapping constraint?

a superclass instance can be a member of more than one subclass (AND)

What is meant by the degree of a relationship?

the number of entities participating in the relationship

What are the 3 degree types?

• Unary (or recursive)

• Binary

• N-ary

What is a unary relationship?

has one participating entity + an association w/ itself

What is a binary relationship?

relationship between 2 entities

What is a ternary relationship?

relationship between 3 entities

What 2 constraints make up the multiplicity of a relationship?

• participation (P) - min. number

• cardinality (C) - max. number

What does the participation constraint determine?

whether all (mandatory) or only some (optional) entity instances participate in the relationship

What does * mean when presenting multiplicity?

many

What does the cardinality constraint determine?

max. number of entity instances to participate in the relationship

What is an example of when an attribute may be assigned to a relationship?

How do you determine the type of binary relationship?

the cardinalities of the associated entities

What are the 3 types of binary relationships?

• one-to-one (1:1)

• one-to-many(1:*)

• many-to-many(*:*)