What are the different models in Database Design?

Flat Model Database

Simple database design consisting of one large table instead of several interconnected tables of a relational database. Called 'flat' because of its only two dimensional (data fields and records) structure, these databases cannot represent complex data relationships. Also called flat file database or flatform database.

Flat files are data files that contain records with no structured relationships. Additional knowledge is required to interpret these files such as the file format properties. Modern database management systems used a more structured approach to file management.

One Example of a flat file is comma seperated file with data

C,110,38,102+,101+,7351.5113.0F562
C,110,38,216+,214+,7351.5113.0B562
C,110,38,57+,56+,7451.5113.0F562

Hierachial Model Database.

A database organization method that is structured in a hierarchy. All access to data starts at the top of the hierarchy and moves downward.In a hierarchical database, records contain groups of parent/child relationships, similar to a tree structure. For example, under a customers name (parent) would be stored the child; description of their last purchase and the date, a child under that would be the individual items purchased, the cost per item and a description of the item, and another child under that would be the items manufacturers name.

Hierarchical databases are fast and simple but inflexible as the relationship is restricted to one-to-many, only allowing for one parent segment per child.

IBM introduced the first generation of database technology, known as hierarchical, when it installed IMS its mainframe computers in the mid-1960s. This technology was upgraded in the mid-1970s to network database technology.

Network Model Database.  

To work around the limitations of hierachial databases , a new model of database design built upon on hierachial design emerged in 1970's.The network model enhanced the hierachial model by allowing records to participate in mutliple parent child relationships. The model is made to accommodate many to many relationships, which allows for a more realistic representation of the relationships between entities. Even though the network database model enjoyed popularity for a short while, it never really lifted of the ground in terms of staging a revolution. It is now rarely used because of the availability of more competitive models that boast the higher flexibility demanded in today’s ever advancing age.

Relational Database Model.

A relation is made up of 2 parts

1.Instance : a table, with rows and columns. Number of Rows = cardinality, Number of fields = degree / arity.

2.Schema : specifies name of relation, plus name and type of each column. E.G. Students(sid: string, name: string, login: string,age: integer, gpa: real).

Example Instance of Students Relation

The concept of a relational database was first developed by Dr. Edger F. (Ted) Codd in A Relational Model of Data for Large Shared Data Banks in 1970. Dr. Codd defined thirteen standards which must be met before a database can be considered to be a relational database

1. A relational DBMS must be able to manage databases entirely through its relational capabilities

2. Information rule: All information in a relational database (including table and column names) is represented explicitly as values in tables.

3. Guaranteed access: Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name.

 4. Systematic null value support: The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain.

5. Active, online relational catalog: The description of the database and its contents is represented at the logical level as tables and can therefore be queried using the database language.

6. Comprehensive data sublanguage: At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.

 7. View updating rule: All views that are theoretically updatable can be updated through the system.

8. Set-level insertion, update, and deletion: The DBMS supports not only setlevel retrievals but also set-level inserts, updates, and deletes.

9. Physical data independence: Application programs and ad hoc programs are logically unaffected when physical access methods or storage structures are altered.

10. Logical data independence: Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures.

11. Integrity independence: The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be bypassed.

12. Distribution independence: Application programs and ad hoc requests are logically unaffected when data is first distributed or when it is redistributed.

13. Nonsubversion: It must not be possible to bypass the integrity rules defined through the database language by using lower-level languages.

The basic data structure of the relational model is the table, where information about a particular entity (say, a student) is represented in columns and rows (also called tuples). Thus, the "relation" in "relational database" refers to the various tables in the database; a relation is a set of tuples. The columns enumerate the various attributes of the entity (the student's name, student id for example), and a row is an actual instance of the entity (a specific student) that is represented by the relation. As a result, each tuple of the student table represents various attributes of a single student.

Dimensional Model

The dimensional model is a specialized adaptation of the relational model used to represent data in data warehouses in a way that data can be easily summarized using OLAP queries. In the dimensional model, a database consists of a single large table of facts that are described using dimensions and measures. A dimension provides the context of a fact (such as who participated, when and where it happened, and its type) and is used in queries to group related facts together. Dimensions tend to be discrete and are often hierarchical; for example, the location might include the building, state, and country. A measure is a quantity describing the fact, such as revenue. It's important that measures can be meaningfully aggregated - for example, the revenue from different locations can be added together.

In an OLAP query, dimensions are chosen and the facts are grouped and added together to create a summary.

The dimensional model is often implemented on top of the relational model using a star schema, consisting of one table containing the facts and surrounding tables containing the dimensions. Particularly complicated dimensions might be represented using multiple tables, resulting in a snowflake schema. A data warehouse can contain multiple star schemas that share dimension tables, allowing them to be used together.Coming up with a standard set of dimensions is an important part of dimensional modeling.

Object database model

In recent years, the object-oriented paradigm has been applied to database technology, creating a new programming model known as object databases. These databases attempt to bring the database world and the application programming world closer together, in particular by ensuring that the database uses the same type system as the application program. This aims to avoid the overhead (sometimes referred to as the impedance mismatch) of converting information between its representation in the database (for example as rows in tables) and its representation in the application program (typically as objects). At the same time, object databases attempt to introduce the key ideas of object programming, such as encapsulation and polymorphism, into the world of databases