DATABASE
DESIGN
1.1 Introduction
Database Design is a collection of process that facilitate the designing, development,
implementation and maintenance of enterprise data management systems. Database Design is
the process of producing a detailed data model of a database. The main objectives of database
designing are to produce logical and physical designs models of the proposed database
system. Database design is the structure a database uses to plan, store and manage data. Data
consistency is achieved when a database is designed to store only useful and required data.
The term database design can be used to describe many different parts of the design of an
overall database system principally and most correctly. It can be thought of as the logical
design of the database structures used to store the dat. In the relational model, these are the
tables and views. In an object database, the entities and relationship map directly to object
classes and named relationships. However, the term database design could also be used to
apply to the overall process of designing, not just the data base structures, but also the forms
and quires used as a part of the overall database application within the Data Base
Management System (DBMS).
1.2 Purpose and Scope
The main objectives of database designing are to produce logical and physical designs
models of the proposed database system.
The logical model; concentrates on the data requirements and data to be stored independent
of physical considerations. It does not concern itself with how the data will be stored
physically. The physical data design model involves translating the logical design of the
database onto physical media using hardware resources and software systems such as DBMS.
1.3 Database Identification
Database table name and column names defined without leaving space.
Lower case used to create database tables name columns names.
Primary Key and foreign Key defined with same name.
4.4 Schema Information
A schema is the structure behind data organization. It is a visual representation of how
different table relationships enable the schema’s underlying mission business rules for which
the database is created. In a schema diagram, all database tables are designated with unique
columns and special features, e.g., primary/foreign keys or not null, etc. Formats and symbols
for expression are universally understood, eliminating the possibility of confusion. The table
relationships also are expressed via a parent table’s primary key lines when joined with the
child table’s corresponding foreign keys.
1.5 Table Definition
users:
Field Name Data Type Size Description
id (PRM KEY) BIG INT 20 Identification Number
user_id BIG INT 20 User ID
User_name VARCHAR 100 User name of the user
password VARCHAR 100 Password to Login
repeat_password VARCHAR 100 Repeat password
email VARCHAR 100 Email ID
acnt_type CHAR 20 Type of the account
date Timestamp Date and time of the account
creation
jobs:
Field Name Data Type Size Description
job_num (PRM KEY) BIG INT 20 Job number
eno (FOREIGN KEY) BIG INT 20 Employer number
job_title VARCHAR 50 Title of job
job_details VARCHAR 200 Details of the job
location VARCHAR 50 Location
salary VARCHAR 10 Salary
aspirants:
Field Name Data Type Size Description
num (PRM KEY) BIG INT 20 Number
ano (FOREIGN KEY) BIG INT 20 Aspirant number
job_num (FOREIGN KEY) BIG INT 20 Employer number
cv MEDIUMBLOB 16MB Curriculum vitae
queries VARCHAR 50 Queries
feedback:
Field Name Data Type Size Description
num (PRM KEY) BIG INT 20 Number
ano (FOREIGN KEY) BIG INT 20 Aspirant number
email-id VARCHAR 40 Email-id
fdcontent VARCHAR 200 Feedback content
1.6 ER Diagram
1.6.1 ER-Diagram Symbols
Name Notation Description
Entity It may be an object with the
1Entity name physical existence or
conceptual existence. It is
represented by a Rectangle.
Attribute The properties of the entity
can be a Ellipse.
Attribute
name
Relationship Whenever an attribute of an
entity refers to another
Relation entity, some relationship
exists. It is represented by a
diamond.
Link Lines link attribute to entity
sets to relation.
Key Attribute An entity type usually has
an attribute whose values are
Key distinct for each individual
attribute entry in the entity set. It is
represented by a Underlined
word in ellipse.
Cardinality Ratio 1) 1:1 It specifies the maximum
2) 1:M number of relationship
3) M:1 instances that an entity can
4) M:M participate in. There are four
cardinality ratios.
ER Diagram
1.7 Database Administration
1.7.1 System Information
Server: XAMPP
Server type: MYSQL
Operating System: Windows 10
Programming languages: PHP, CSS, HTML, Java Script
1.7.2 DBMS Configuration
Part of the DBMS installation process is the connection of the DBMS to other system
software components that must interact with the DBMS. Typical infrastructure software are
that may need to be configured to work with DBMS includes networks, transaction
processing monitors, message queues, other types of middleware, programming languages,
Systems management software, Operations and job control software Web servers, and
application servers.
1.7.3Support Software Required
The system installs MYSQL server while installing XAMPP software. All the backup content
stores in MySQL data folder.
1.7.4 Storage Requirements
Storage for database continues a set of compatible software and hardware where
database files are stored.
Compatibility must be carefully tested to ensure eliminating bottlenecks and the
possibility of data corruption.
Failure free work in high load conditions and the redundancy of vulnerable
components must be provided. Such a set must meet the highest standards in terms of
performance and reliability, to ensure continuous and fast access to important data.
1.7.5. Backup and Recovery
A volatile storage like RM stores all the active logs, disk buffers, and related data. In
addition, it stores all the transactions that are being currently executed. If such a volatile
storage crashes abruptly, it would take away all the logs and active copies of the database
makes recovery almost impossible, as everything that is required to recover the data is
lost.
Following techniques may be adopted in case of loss of volatile storage-
We can have checkpoints at multiple stages so as to save the contents of the database
periodically.
A state of active database in the volatile memory can be periodically dumped onto a
stable storage, which may also contain logs and active transactions and buffer blocks.
When the system recovers from a failure, it can restore the latest dump.
It can maintain a redo-list and an undo-list as checkpoints.
It can recover the system by consulting undo-redo lists to restore the state of all
transactions up to the last checkpoint.