*CH4: Information management
4.1 Conceptual data modelling: UML class diagrams
= Unified modelling language, standard language in field of IT
- Contains lot of diagrams: we only look in class diagrams
- Material is on other site
- Handouts are available there (if it says optional don't do)
4.2 Logical modelling
=> Row 3, “what”-column in Zachmann's framework
Logical data model
- Usually derived from the more abstract conceptual data model
- Implemented as physical model
- 2nd row Zachman: corresponds to owners view, or the enterprise model
- Here is where trough requirements engineering, the conceptual model is
created
- 3rd row: IS functionality is taken into consideration,logical design of DB
(relational model is created)
- 4th row: physical model is developed, how information will be stored on disk
(will take into account the technical choices that have been made for the
implementation platform) => 4th and further rows out of scope
= Development of a database design
=> Different paradigms exist
- Relational databases
- Hierarchical databases
- Networks databases
- Object-oriented databases
- …
=> Relational model:
- underlying model for SQL databases
- Most widespread model
The relational model: basic concepts
- Information is stored in tables
- This table of values is called a relation (or table)
- Each row in table is called a tuple or record
- The column names are attributes
- A single cell corresponds to an attribute value
- Table names and column names should help interpret the meaning of the
values in each row
=> See example sl6
1
, Example sl8:
- Table name = customer, stores information about customers
- Each record or tuple is the information of one customer
- Column headings are address, city, name, country… (attributes)
- Cells are the values that correspond to the attributes
Relational database principles
- Each cell holds 1 value: no lists
=> In example sl10: is wrong, you can't put list of order ID’s in one cell
=> You will use one where they are split up, a row for each order (each cell
needs to be filled, so you have to duplicate all the other info each time)
- Each cell holds single value: No “row in table” of “table in table
=> In example sl11: in cell for address, they put another table (bad)
=> Put the information as different columns
- Information is not stored in a single table
- Information is divided across multiple tables, so as to avoid duplication of
information
=> Such as to avoid problems: when updating, inserting new or deleting info
Update problems
- Consider table sl13, contains information about orders, order details and
products
- What is price of product Chang changes: 6 rows need to be updated because
the ordered it, if table is bigger you have a huge problem
Insert problems
- Where do you register productID, ProductName and Price of a new product
that has not been ordered yet?
- You can try add a row, but it won’t work because you don’t have ID’s
Delete problems
- What happens to the information of product 7 if you delete order 10262 (The
only order of 7, so it will disappear out of the table)
Normalisation
- For good relational database design, the information in the database will be
split across several tables (minimise redundancy (duplication) and anomalies
and inconsistencies)
- Process of splitting tables = normalisation
=> Base on functional dependencies between attribute types
=> functional dependency: the fact that the value of one attribute uniquely
determines the value for another attribute (customerName is functional
dependent on CustomerID)
2
4.1 Conceptual data modelling: UML class diagrams
= Unified modelling language, standard language in field of IT
- Contains lot of diagrams: we only look in class diagrams
- Material is on other site
- Handouts are available there (if it says optional don't do)
4.2 Logical modelling
=> Row 3, “what”-column in Zachmann's framework
Logical data model
- Usually derived from the more abstract conceptual data model
- Implemented as physical model
- 2nd row Zachman: corresponds to owners view, or the enterprise model
- Here is where trough requirements engineering, the conceptual model is
created
- 3rd row: IS functionality is taken into consideration,logical design of DB
(relational model is created)
- 4th row: physical model is developed, how information will be stored on disk
(will take into account the technical choices that have been made for the
implementation platform) => 4th and further rows out of scope
= Development of a database design
=> Different paradigms exist
- Relational databases
- Hierarchical databases
- Networks databases
- Object-oriented databases
- …
=> Relational model:
- underlying model for SQL databases
- Most widespread model
The relational model: basic concepts
- Information is stored in tables
- This table of values is called a relation (or table)
- Each row in table is called a tuple or record
- The column names are attributes
- A single cell corresponds to an attribute value
- Table names and column names should help interpret the meaning of the
values in each row
=> See example sl6
1
, Example sl8:
- Table name = customer, stores information about customers
- Each record or tuple is the information of one customer
- Column headings are address, city, name, country… (attributes)
- Cells are the values that correspond to the attributes
Relational database principles
- Each cell holds 1 value: no lists
=> In example sl10: is wrong, you can't put list of order ID’s in one cell
=> You will use one where they are split up, a row for each order (each cell
needs to be filled, so you have to duplicate all the other info each time)
- Each cell holds single value: No “row in table” of “table in table
=> In example sl11: in cell for address, they put another table (bad)
=> Put the information as different columns
- Information is not stored in a single table
- Information is divided across multiple tables, so as to avoid duplication of
information
=> Such as to avoid problems: when updating, inserting new or deleting info
Update problems
- Consider table sl13, contains information about orders, order details and
products
- What is price of product Chang changes: 6 rows need to be updated because
the ordered it, if table is bigger you have a huge problem
Insert problems
- Where do you register productID, ProductName and Price of a new product
that has not been ordered yet?
- You can try add a row, but it won’t work because you don’t have ID’s
Delete problems
- What happens to the information of product 7 if you delete order 10262 (The
only order of 7, so it will disappear out of the table)
Normalisation
- For good relational database design, the information in the database will be
split across several tables (minimise redundancy (duplication) and anomalies
and inconsistencies)
- Process of splitting tables = normalisation
=> Base on functional dependencies between attribute types
=> functional dependency: the fact that the value of one attribute uniquely
determines the value for another attribute (customerName is functional
dependent on CustomerID)
2
