Database Design Concepts

MODULE 5 Logical and Physical Database Design Concepts

MMS 144 Group 4 Presentation

Database Design Components

This presentation provides an explanation of database elements and gives a better understanding of key topics, terms and concepts, including tables, columns, rows, keys, constraints, and the components of a logical and physical design.

indexes

schema object

data schema

tables

Some Database Terms To Know

columns

fields

keys

rows

record

relationships

data types

A schema is simply a group of related objects in a database. Within a schema, objects that are related have relationships to one another.

Three Models Associated To A Schema

TIME

PRODUCT

SALES

STORE

Conceptual Data Model It is the basic database model, which deals with organizational structures that are used to define database structures such as tables and constraints. This model identifies the highest-level relationships between the different entities.

Logical Data Model It is developed to enrich a conceptual model by defining explicitly the columns in each entity and introducing operational and transactional entities. This model describes the data in as much detail as possible, without regard to how they will be physically implemented in the database

DIM_TIME

DIM_PRODUCT

FACT_SA LES

DIM_STORE

Physical Data Model It describes a database-specific implementation of the data model and offers database abstraction and helps generate the schema. It shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables

Comparison of the Three Data Models

FEATURES

COLUMN NAMES

TABLE NAMES

COLUMN DATA TYPES

FOREIGN KEYS

PRIMARY KEYS

ATTRIBUTES

ENTITY RELATIONSHIPS

PHYSICAL

LOGICAL

CONCEPTUAL

ENTITY NAMES

A table is a 2D structure composed of rows and columns that is the primary unit of storage in a relational model. It requires physical storage on the host computer for the database.

Four Types of Tables

Data Tables stores most of the data found in a database. Join Tables are used to create a relationship between two tables that would otherwise be unrelated. Subset Tables contain a subset of data from a data table. Validation Tables often referred to as code tables are used to validate data entered into other database tables.

Columns

represent one attribute for that entity

Rows

represent one occurrence of the entity that the table represents

Normalization

It is the process of mapping the entities in the conceptual design to tables in the logical design; basically the process of organizing data in a database, one of the main ways is through making a table or even multiple tables.

Tablespace

A logical structure used to store relational tables; multiple tables can be placed in a single tablespace, while large tables might have their own tablespace or are divided into smaller tables and placed in multiple tablespaces

the process of dividing tables into smaller pieces

Partitioning

Common Data Types

Integers

A whole number that can be positive or negative or zero value. It cannot be a fraction nor have decimals

Refers to any numbers, letter, space or symbol that can be input in a computer put together in a sequence

String

Floating Point Number

A number that has decimals, or has a fractional part

Common Data Types

Contains a group of elements that could be of the same data type. It is used to organize data for sorting and searching related sets of values

Array

Variable characters which have characters of intermediate length, Varchar is a data type for a field (or column) in a database management system that may store both letters and digits

VarChar

INSERT INTO Demovarchar VALUES('Newton Hamilton', 'Isaac','M','Design Head',69)

Boolean

Used for creating true or false statements

TABLE 2

PK

PRIMARY KEY

TABLE 3

PK

PRIMARY KEY

TABLE 4

PK

PRIMARY KEY

TABLE 6

PK

PRIMARY KEY

TABLE 5

PK

PRIMARY KEY

FK

FOREIGN KEY

TABLE 1

PK

PRIMARY KEY

FK

FOREIGN KEY

FOREIGN KEY

FK

FK

FK

FOREIGN KEY

FOREIGN KEY

Keys

They control the integrity of the information stored in a database. Keys are column values in a table that are used to either uniquely identify a row of data in a table, or establish a relationship with another table.

Primary Keys

The combination of one or more column values in a table that make a row of data unique within the table

PRIMARY KEY

Student ID

Student ID

Last Name

Middle Name

First Name

Enrollments

Student ID

Class ID

Student ID

Enrollment ID

Classes

Class ID

Class ID

Description

Title

Class ID

FOREIGN KEYS

JOIN TABLE

PRIMARY KEY

CUSTOMER

PK

Foreign Keys

CustID

CustLast

CustFirst

FOREIGN KEY

ORDER

CustID

OrderNo

Quantity

OrderDate

FK

CustStreet

The combination of one or more column values in a table that reference a primary key in another table.

CustApt

CustCity

CustState

CustZip

CustPhone

Constraints

• Used to specify rules for data in a table
• Used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

• Can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

Primary Key Constraints

Primary Key

Employee Id must be UNIQUE

Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

Referential Constraints

Constraint that enforces a relationship among tables in a database. Makes use of the foreign key and ensures that it has a corresponding primary key in the parent table.

taking reference

Primary Key

Foreign Key

This example defines a referential constraint where the referenced and referencing tables have a 1-to-1 correspondence.

Course Table

Student/Base Table

The table containing primary key is called a PARENT TABLE or REFERENCED TABLE. The table containing foreign key is called as CHILD/REFERENCING TABLE.

Intersection Table Constraints

Form many to many relationships through the use of relational databases Many rows in different tables may be related to one another; an intersection table and two one-to-many relationships

FOREIGN KEY

EMPLOYEES

FOREIGN KEY

FOREIGN KEY

PROJECTS

EMPLOYEES_PROJECT

*Eployee ID

*Eployee ID

*ProjectNum

Last Name

*ProjectNum

Project Title

First Name

This intersection or junction table holds the primary key field from each of the tables in the many-to-many relationship.

Integrity Constraints

Promotes the accuracy of the data in the database. Ensures that authorized users of a DBMS will not cause any accidental damage to the database.

Major Types

NOT NULL

Does not allow NULL Values

NULL Values allowed

NOT NULL Constraints A null value in a relational database is a special code that can be placed in a column that indicates that the value for that column in that row is unknown

CHECK Constraints use a simple logic statement to validate a column value. The outcome of the statement must be logical true or false, with an outcome of true allowing the column value to be rejected with an appropriate error message.

CREATE TABLE dbo.Student ( StudId INT NOT NULL IDENTITY(1,1), NAME VARCHAR(100), Age INT CHECK (Age > 18), Grade VARCHAR(5) ) OR CREATE TABLE dbo.Student ( StudId INT NOT NULL IDENTITY(1,1), NAME VARCHAR(100), Age INT , Grade VARCHAR(5), CONSTRAINT Check_Student_Age CHECK(Age > 18) )

Other Types of Integrity Constraints

Domain Constraint

• contains a set of rules or conditions to restrict the kind of attributes or values a column can hold in the database.

ERROR!

• Only data types with the integer value are allowed in the column. 'a' is a character data type.

Other Types of Integrity Constraints

Referential Integrity Constraint

• ensures that there must always be a valid relationship between two relational databases

Track Table

Student Table

ERROR!

• Track_ID '4' does not exist in the Track Table thus violating the referential key constraint.

Other Types of Integrity Constraints

Key Constraint

• ensures that no two keys have the same value - keys must always be unique

• The Track_ID (the primary key column for this table) has values that are the same.

Triggers a module of programming logic that “fires” (executes) when a particular event in the database takes place

A trigger in the student database in which whenever subjects marks are entered, before inserting this data into the database, trigger will compute those two values and insert with the entered values.

In the student record a trigger will allow the total and average of specified marks automatically inserted whenever a record is inserted.

Query

View

A virtual table that contains a query. It acts as a table but it does not store any data.

Functions:

• Hide columns/rows that are unneeded.
• Hide complex database operations
• Improve query performance

References

Assaf, W. (2022, April 3). Primary and foreign key constraints - SQL server. SQL Server | Microsoft Docs. Retrieved from https://docs.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-ver15 BBC (n.d). Introducing databases. Retrieved from https://www.bbc.co.uk/bitesize/guides/zfd2fg8/revision/6#:~:text=Common data types&text=integers (whole numbers)%2C for,%3A abc%2C def456%2C 3erf78!%40 Database design process - USNA. IT360: Applied Database Systems. (n.d.). Retrieved March 2022, from https://www.usna.edu/Users/cs/adina/teaching/it360/spring2012/slides/it360_Set6_ERToRel.pdf IBM. (2021, July 13). Types of Constraints. IBM. Retrieved from https://www.ibm.com/docs/en/db2-warehouse?topic=constraints-types IBM Integrated Analytics System (2012). NOT NULL constraints. Retrieved from https://www.ibm.com/docs/en/ias?topic=constraints-not-null Oppel, A. (2010). Databases Demystified (2nd ed.) (pp.37-54, 19). New York: McGraw-Hill. Singh, N. (2022). Module 2: Data Models in DBMS (What are Integrity Constraints in DBMS?). Retrieved from https://www.scaler.com/topics/dbms/integrity-constraints-in-dbms/#what-are-integrity-constraints-in-dbms- Stephens, R. (2003). The Elements of Database. Retrieved from https://www.developer.com/database/the-elements-of-a-database/ Teach Computer Science (n.d). Database Data Types. Retrieved from https://teachcomputerscience.com/database-data-types/ Tech on the Net (n.d.). SQL Server: Check Constraints. Retrieved from https://www.techonthenet.com/sql_server/check.php Technopedia (2018). Database Column. Retrieved from https://www.techopedia.com/definition/8/database-column#:~:text=In%20the%20context%20of%20relational,documents%20or%20even%20video%20clips Technopedia (2017). Data Type. Retrieved from https://www.techopedia.com/definition/3349/data-type W3Schools. (n.d). SQL Constraints. Retrieved from https://www.w3schools.com/sql/sql_constraints.asp

Group Members

Research

Dadios Ivy Chanelle

Biglete Jazmin Gaea

Capistrano Anna Pamela

Almirante Juana Sophia

Tabuquilde Shaine

Protusada Janelle Mica

Villanueva Cyrille

Fojas April Felicia

Research/Editing

Dimaano Cherry Mae