|
|
|
|
|
|
A Data model describes the structure of a database's information content.
The three kinds of models are:
1. Conceptual
Data Model is a detailed specification of organizational data independent
of any database technology. It defines the whole database without
reference to how data are stored in a computer's secondary memory. It is
depicted in graphical form using entity-relationships
2. Logical
Data Model This is the relational model first introduced by Edgar
F. Codd in 1970. It consists of three components: To download
software to draw logical data models: Download
Logic Model
a. Data Structure.
Data organized in the form of tables with rows and columns
b. Data Manipulation. Operations
using SQL
c. Data Integrity.
Specifying business rules to maintain the integrity of data
3. Physical
Data Model is how the logical (relational) model is represented in
storage.
We start with the conceptual data model, use it to prepare the logical
(relational), and then use that to prepare the physical data model.
For an example to transit from logical to physical model, click here:
Logical
to Physical
For a slide show display, click here: Slide
show
Logical database design is the process of transforming the conceptual data model into logical database model The objective is to accomplish a database that allows data sharing, flexibility, and ease of access
Hierarchical - found in older legacy systems
top-down, parent
child structure
a child may
be related to only one parent
Network - found in older legacy systems
all entities
are 'equal'
an entity may
be associated with many other entities
Relational - most common
tables of columns
and rows
no physical
data structures, only logical
Object-oriented - the way of future
objects can be data, methods, pictures, video etc.
A Database Schema is a structure which contains descriptions of objects
created by a user, such as base tables, views, constraints, etc., as part
of a database. For some examples
Database
Schema
Biomedical
Informatics Research Network Schemas
We have conceptual schema, logical schema, and physical schema.
Attribute is a property or characteristic
of an entity type that is of interest. It is a named column (field)
of a relation.
Entity is a person, place, object, event, or concept whose data
is maintained.
Entity type is a collection of entities that share common properties
or characteristics. A single occurrence of an entity type is known
as Entity instance.
Entity class is a group of entities.
In terms used in Java and Access, Attribute is a field or column, entity
is a record or row, and entity class is a file. Attributes have values.
They are data points or cells. Attributes also have types.
In Access, they are :
1. Text which can be used for any alphanumeric, that
is letters or numbers not used in calculation. There are no rules,
so it is
not incorrect to se parentheses,
hyphens and any symbols. We can state the maximum size and whether
it is required.
2. Memo
3. Number used for numbers used in calculation.
Here we cannot use blank spaces, parentheses, hyphens, or any other
symbols. They can either
be integers (whole numbers) or float (with decimal points). Based
on the size the integers are
divided into Byte, Integer, and
Long Integer, and the floats into single and double. For these, we
can also specify the
number of decimal places from
a minimum of zero to a maximum of fifteen. The default value will
be zero and we can
state whether it is required.
4. Date/Time which is a composite
5. Currency where the dollar sign, commas if required,
the decimal point and two places after will be automatic.
6. Auto Number which can either be increment (default)
or random.
7. Yes/No or True/False or On/Off
8. OLE Object
9. Hyperlink
10. Lookup Wizard which cannot be used for a blank data base.
One or more keys can be assigned as Primary key(s). Primary keys are not required but integrity and replication problems are better maintained with primary keys. Primary keys cannot by blank (null) and they cannot be duplicated. If there are more than one primary key, a single item may be duplicated but the combination cannot be. For instance, if the first and last names are joint primary keys, there can be many individuals with the same first or the same last name but one individual with the same first and last name. Primary keys may be assigned for a specific purpose only, and may be local to that organization, or be global like a social security number.
Relationship is the association between different entities. For instance, a student (entity) has a relationship with another entity, say a course. A student takes one or more course(s), or a course is taken by students. These are known as Entity-Relationships, and the model that depicts them is called a E-R Model. Relationships are the glue that hold the various components of an E-R Model. A relationship type is a meaningful association between (or among) entity types. A Relationship Instance is an association between (or among) entity instances, where each relationship instance includes exactly one entity from each participating entity type. Note that relationships are not attributes. Generally, - is a - means a relationship, while - has a - means an attribute. There is a relationship between a student and a course, while a student has a last name (attribute) A relationship may have its own attribute.
For tips on drawing E-R Diagrams, click here: E-R Diagrams
Properties of relationships.
Cardinality
Cardinality defines the numeric relationships between occurrences of the
entities on either end of
the relationship line. A cardinality constraint restricts the
number of members in the set. It specifies the number of instances
one one entity that can (or must) be associated with each instance of another
entity. It has a minimum (may be zero) and a maximum that must be
non-negative integers. The three types of cardinality constraints
used in ER Models are:
1. One-to-one.
2. One-to-many
3. Many-to-many
If a relationship is required, it is known as a mandatory participation constraint. For example, every patient must have one or more patient history, and every patient history must belong to a patient, or every student must take one or more courses and every course must be taken by at least one student. If a relationship is not required, it is known as a optional participation constraint. For instance, a person amy or may not be married to another person. It is also possible to have one participation mandatory, and one optional. An employee may or may not be assigned to a project optional) but any project must be assigned to an employee (mandatory)
A Degree of a relationship is the number
of entity types that participate in a relationship. The different
degrees of relationships are:
1. Unary or Recursive relationship is a relationship
between the instances of a single entity type. An one-to-one relationship
is a person is married to. A one-to-many relationship is an employee
manages.
2. Binary Relationship is a relationship between
the instances of two entity types, and is the most common type of relationship
encontered in data modeling. For example, an one-to-one relationship
is between an employee and a parking place, (each employee is assigned
exactly one parking place, and each parking place is assigned to exactly
one employee), a one-to-many relationship between a product and a product
line. Each product is assigned to only one product line, but a product
line may have any number (including zero) products. A many-to-many
relationship is between students and courses. A student may take
many courses, and a course may be taken by many students.
3. Ternary Relationship is a simultaneous
relationship among the instances of three entity types. This
is complex and if possible should be broken into three binary relationships.
An example is where a vendor, a part, and a warehouse are all related and
a supply schedule may be used to relate to all the three entities.