02DatabaseDesign

Database Design

Outline

Database Design Process
Database Domain
Database Schema
Object Modeling

A simplified process of database design used in this subject, consists of the following stages: conceptual modelling, logical design, and physical design.

A stage of conceptual modelling, transforms a specification of database domain into a conceptual schema.

Next, a stage of logical design, transforms a conceptual schema into a logical schema. If a tabular data model is used as a logical view of a database, a conceptual schema is transformed into a set of so called relational schemas. A relational schema consists of sequence of attributes located at a header of table with data.

Finally, a stage of physical design determines the implementation details, like for example, location of the tables with data on the persistent storage devices.

A picture at the bottom of the slide visualises a process of database design. A real estate agency would like to have a database with information about the real estate properties available for sale. A database domain contains information about real estate properties, owners of the properties, and the specific features of the properties, like for example, a fact that a property is located among the trees.

In the first stage of a design, a database domain is more formally represented by a conceptual schema. A conceptual schema is usually a diagram, that consists of the rectangular boxes representing classes of objects, labelled lines representing associations between the objects, and the attributes (properties) describing the classes of objects.

At a stage of logical design the classes of objects, association and attributes are transformed into the relational tables, that contain the rows with information about people (owners), houses, and locations of houses.

Finally, a stage of physical design determines the locations of tables on persistent storage devices, and the additional persistent storage structures like for example indexes or materialized views.

Database Design Process

A simplified process of database design consists of the following stages:
- Conceptual modeling
- Conceptual modeling transforms a specification of a database domain into a conceptual schema
- Logical design
- Logical design transforms a conceptual schema into a logical schema, for example the headers of relational tables
- Physical design
- Physical design determines the implementation details and adds to a logical schema persistent storage structures that improve perfromance, for example the indexes, clusters, partitions, materialized views, and the others

HDD-schema

Database Design

A database domain is a selected fragment of the real world, to be described by the contents of a database.

For example, a typical simple business domain can be described in the following short story. A company would like to store and to maintain information about its suppliers and about the parts shipped by the suppliers. A supplier is described by a supplier name, date of birth, salary and city he or she lives in. A part is described by a part number, part name, colour and price. A shipment is described by a supplier number, part name and quantity ordered. A supplier is identified by its supplier number and a part is identified by its part number.

A short story included in the present slide is not the only way how a database domain can be described. It is possible to create a functional description of a database domain. A functional description lists the database applications to be implemented. The database applications provide information about data to be retrieved from a database. For example find all supplier who supply a part with given name. It is contributes to specification of classes of objects and descriptions with attributes.

Yet another way how a database domain can be described is to refer to an already existing database and to list the ways how an old database should be transformed into a new database. For example, we can take an already existing hospital database and replace patients with vehicles, doctors with mechanics, nurses with junior mechanics to get a vehicle maintenance and repair facility database.

Database Domain

A database domain is a selected fragment of the real world to be described by the contents of a database
For example, a typical simple business domain can be described as a sequence of statements:
- A company would like to store and to maintain information about its suppliers and the parts shipped by the suppliers
- A supplier is described by a supplier name, date of birth, salary, and city he/she lives in
- A part is described by a part number, part name, colour and price
- A shipment is described by a supplier number, part name and the quantity of ordered parts
- A supplier is identified by its supplier number and a part is identified by its part number

Database Design

Outline

Database Design Process
Database Domain
Database Schema
Object Modeling

A database schema is a description of data stored in a database expressed in the terms of a particular data abstraction level.

For example, at the highest level of abstraction, a database schema is a conceptual schema. A conceptual schema is a description of stored data expressed in the terms of classes objects, properties of objects, identifiers of classes of objects, associations between the classes of objects and the others.

At the middle level, a database schema is a logical schema. A logical schema based on tabular view of data is a description of stored data expressed in the terms of attributes, values of attributes, rows, columns, headers and tables.

At the lowest level, a database schema is a physical schema. A physical schema is a description of stored data expressed in the terms of files, indexes, clusters, data blocks, and the others.

Database Schema

A database schema is a description of stored data expressed in the terms of a particular data abstraction level
- For example, a conceptual schema is a description of stored data expressed in the terms of classes objects, properties of objects, identifiers of classes of objects, associations between the classes of objects, multiplicities of attributes and associations and generalization hierarchies
- For example, a logical schema is a description of stored data expressed in the terms of attributes, values of attributes, rows, columns, headers and tables
- For example, a physical schema is a description of stored data expressed in the terms of files, indexes, clusters, partitions, materializations, segments, extents and data blocks

Database Design

Outline

Database Design Process
Database Domain
Database Schema
Object Modeling

The object modeling is a special kind of conceptual modeling, where a specification of database domain is transformed into a simplified class diagram (conceptual schema).

The principles of object modeling are the following:

Contents of a database is quantised into discrete objects. A database is a description of a selected fragment of the reality. The reality is full objects. Hence, objet modeling provides a view of the reality as a collection of linked objects. For example, a student is an object, a lecturer, is an object, etc

Objects are described by the attributes (properties) and the operations (methods). In our case, there is no need to represent the operations on objects and attributes. It is possible to implement the operations, however, in our case we shall ignore the operations.

Objects are identified by the values of selected attributes. Identification of the objects is a very important property of the model. Identification is a reflection of a simple rule use in the reality sayin that when we talk about an object then we must identify it well, such that a listener knows well what we are talking about.

A class of objects is a group of homogeneous objects with common properties, common semantics and common identifiers.

Object Modeling

Object modeling is a special kind of conceptual modeling where a specification of a database domain is transformed into a simplified class diagram (conceptual schema)
Principles of object modeling:
- Contents of a database is quantised into discrete objects
- Objects are described by the attributes (properties) and operations (methods)
- Good news: we ignore the operations !
- Objects are identified by the values of selected attributes
- A class of objects is a group of homogeneous objects with common properties, common semantics and common identifiers

Here are few examples of the concepts introduced on the previous slide.

We usually start from identification of a single object. In our example a student is an object, a lecturer is an object, and a lecture hall is an object.

It is possible to think about a shipment is an object. Well, it is not a solid object, we can touch, move, etc but still a process of transferring goods from one place to another can be considered as an object. If we consider a process as an object then even an accident can be considered as an object.

Next, we are looking for a description of an object. In our case an object can be described as a collection of values of attributes. For example, a student can be described by the attributes like student number, first name, last name, date of birth and the others.

Next, we have to pick the attributes, that can use fro a unique identification of an object. For example, a student is identified by an attribute student number, a lecture hall is identified by a pair of attributes (building number and room number), a shipment is identified by a triple of attributes (supplier, part, and shipment date).

Now, we consider a group of instances of objects of the same kind, for example a group of students, and we call such group of homogeneous objects as a class objects STUDENT.

In the same way, a group of lecturers forms a class of objects LECTURER, a group of shipments forms a class of objects SHIPMENT.

Object Modeling

Examples:
- A student is an object, a lecturer is an object, a lecture hall is an object, a shipment is an object, an accident is an object, etc
- A student is described by the attributes student number, first name, last name, date of birth, etc
- A student is identified by a student number, a lecture hall is identified by building number and room number, a shipment is identified by a supplier name, date, and time, etc
- A group of students forms a class STUDENT, a group of lecturers forms a class LECTURER, a group of shipments forms a class SHIPMENT, etc

Now we define more object modeling concepts. A description of the selected portion of the reality with the classes of objects and attributes describing the classes is not sophisticated enough to represent relationships between the objects. Usually the instances of objects are linked one with each other. The links may represent interaction between the objects, dependency of one object on another, transfer of information between the objects, etc.

In general a link is a conceptual connection between two or more objects. For example, "James talks to Janusz", "Lecture 1 on Database Systems is in Building 3, Room 2", "Peter supplies bolts to James".

Like with the instances of objects, we generalize a collection of homogeneous links into an association. An association represents a group of homogeneous links with a common structure, common attributes, common semantics and common identifiers. For example the links like "James talks to Janusz", "Jane talks to Willy", etc can be represented as an association "STUDENT Talks-to LECTURER". In the same way we can create the associations like "LECTURE Is in BUILDING", SUPPLIER Supplies PART to MANUFACTURER and so on. The first two associations are binary associations because each one of them involves only two classes of objects. The third association is a ternary association because it involves three classes of objects: SUPPLIER, PART and MANUFACTURER.

It s possible that a binary association has the same left and right hand side. For example EMPLOYEE Supervises EMPLOYEE. Such association is still a binary association and it consists of links between the instances of objects in a class EMPLOYEE.

Object Modeling

More object modeling concepts:
- A link a conceptual connection between two or more objects
- An association represents a group of homogeneous links with a common structure, common attributes, common semantics and common identifiers
Examples of links:
- James talks to Janusz
- Lecture 1 in CSIT115 is in building 3 room 2
- Peter supplies bolts to James
Examples of associations:
- STUDENT Talks-to LECTURER
- LECTURE Is-in BUILDING
- SUPPLIER Supplies PART To MANUFACTURER

An instance diagram is a very convenient visualization of instances of objects, links between the objects and classes of objects. An instance of an object is represented by an icon, for example, the icons of students represent instances of students and icons with the codes of subjects represent instances of subjects.

The lines linking the icons represent the links between the instances of objects. Note, that in our example some students have to links to different subjects and some students do not have any links to the subjects. It means that some objects do not need to be linked to other objects. If in our case, the links represent the facts like "student enrolled subject" then student without any links enrolled no subjects.

All icons representing instances of objects of the same type are included in a n oval representing a class of objects and a label representing a name of a class of objects. In our instance diagram, we have two classes of objects STUDENTS and SUBJECTS.

A collection of homogeneous links represent an association between the classes of objects.

Object Modeling

Visualizations of objects, links, classes, and associations

HDD-schema

An important relation between the classes of objects is a "set inclusion" relation also called as "is a subset" relation or simply "is a" relation.

A number of "set inclusion" or "is a subset" relations is represented as "generalization hierarchy". A single level in a generalisation hierarchy represents a single "set inclusion" or "is a subset" relation between the classes of objects. For example, if a set of all objects in a class X "is a subset" of a set of all a objects in a class of objects Y (or if X "is included in Y") , then class of objects Y is a generalization of class of objects X.

In other words, if a class of objects Y is a generalisation of class of objects X, then a set of all objects in Y "includes" a set of all objects in X. If X is a generalization of Y then we shall simply say, that X "is a" Y.

Here are a few examples of generalisation.

A class of objects STUDENT is a generalization of classes UNDERGRADUATE STUDENT and POSTGRADUATE STUDENT.

It is because, a set of all undergraduate students "is a subset" of a set of all students, and a set of all postgraduate students "is a subset "of a set of all students.

In the other words, a set of all students includes a set of all postgraduate students, and it also includes a set of all undergraduate students.

At the end we can simply say that UNDERGRADUATE STUDENT IS-A STUDENt and POSTGRADUATE-STUDENT IS-A STUDENT.

Object Modeling

More object modeling principles:
- A generalization hierarchy represents Is-a-subset relation between the classes of objects
- If a set of all objects in a class X is a subset of a set of all objects in a class Y then class Y is a generalization of class X
- In other words, if a class Y is a generalization of class X then a set of all objects in Y includes a set of all objects in X
Example of generalization:
- A class STUDENT is a generalization of classes UNDERGRADUATE STUDENT and POSTGRADUATE STUDENT
- This is so, because a set of all undergraduate students is a subset of a set of all students and a set of all postgraduate students is a subset of a set of all students
- In other words, a set of all students includes a set of all postgraduate students and it also includes a set of all undergraduate students

An instance diagram can be used to visualize generalization hierarchies. The classes of objects visualized as blobs that includes all icons of instance of objects and with an attached class name label can be "nested" in each other. A single nesting represents a single level in a generalization hierarchy.

An instance diagram given on the present slide visualises the concepts of classes of objects and generalization of classes of objects. A class STUDENT is a generalization of the classes UNDERGRADUATE STUDENT and POSTGRADUATE STUDENT. It is because every undergraduate student is a student, and every postgraduate student is a student as well. A blob with the icons of undergraduate students is included in a blob withe the icons of students. A blob with the icons of postgraduate students is included in a blob withe the icons of students.

A class STUDENT is called as a superclass and the classes UNDERGRADUATE STUDENT and POSTGRADUATE STUDENT are called as subclasses.

Note, that the blobs of undergraduate students and postgraduate students do not have common elements. It is because a student cannot be in the same moment an undergraduate and postgraduate student. If subclasses are disjoint then a generalization hierarchy is called as "disjoint". Otherwise it is called as 'overlapping". Fo example, a subclass of students who enrolled a subject CSIT115 and a subclass of students who enrolled a subject CSIT114 are overlapping because some students enrolled both classes.

If a union of subclasses is equal to a superclass then such generalization is called as complete. Otherwise a generalization is partial. A generalization UNDERGRADUATE STUDENT IS-A STUDENT and POSTGRADUATE STUDENT IS-A STUDENT is complete because there are no other students who are not undergraduate and postgraduate,

Object Modeling

Visualizations of generalizations UNDERGRADUATE STUDENT ISA STUDENT and POSTGRADUATE STUDENT ISA STUDENT

HDD-schema

A few more examples of generalisation.

A class HUMAN is a generalization of classes STUDENT and LECTURER.

It is so because a set of all students is a subset of a set of all humans, and a set of all lecturers is a subset of a set of all humans

In other words, a set of all humans includes a set of all students, and it also includes a set of all lecturers (what some students do not believe that it is possible).

We can also say that SUDENT IS-A HUMAN and (surprise, surprise, ...) LECTURER IS-A HUMAN as well.

My favorit example of generalizationis the followinfg.

A class BAT is a generalization of classes GREY-BAT, VAMPIRE-BAT, and BATMAN

It is so because a set of all grey bats is a subset of a set of all bats, and a set of all vampire bats is a subset of a set of all bats,and a set of all batmen is a subset of a set of all bats.

Object Modeling

Another example of generalization:
- A class HUMAN is a generalization of classes STUDENT and LECTURER
- This is so because a set of all students is a subset of a set of all humans and a set of all lecturers is a subset of a set of all humans
- In other words, a set of all humans includes a set of all students and it also includes a set of all lecturers
Yet another (... and it is my favorite ...) example of generalization:
- A class BAT is a generalization of classes GREY-BAT, VAMPIRE-BAT, and BATMAN
- This is so because a set of all grey bats is a subset of a set of all bats and a set of all vampire bats is a subset of a set of all bats and a set of all batmen is a subset of a set of all bats

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapter 2, Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 10 Database System Development Cycle, Pearson Education Ltd, 2015