Download Database Design and Implementation using MySQL and phpMyAdmin: A Case Study and more Study Guides, Projects, Research Database Management Systems (DBMS) in PDF only on Docsity!
Database Design: Database design is the process of creating the blueprint of a database using different designing tools to meet the requirements of a clients and later on is turned into actual physical database. We cannot directly make physical database but if we do so, there will be lots of error in it which has to debugged to make it free from bugs and errors. It is a design which everyone can understand which helps client to understand it easily and say if anything should be removed or added before implementing into physical database. In the database model only we define primary key, entities, attributes, foreign key, and create relationships between the data of two tables. When databases are built from a well-designed data model the resulting structures provide increased value to the organization. Database obtained from the data model has minimum data redundancy, maximum data integrity, stable, consistency, easily accessible and shareable, and better usability. Building database from the data model results in proper and easy database construction as there will be proper understanding of how data is stored in a database. The process of designing the database is:
1. Conceptual Design A conceptual model is basically ER diagram. In this developmental phase, all types of information given by the client is gathered and is divided in entities which later on becomes table. Then, the information items that is to be stored in a table is gathered and items are converted to the attribute of an entity. In this phase only, relationships between the entities are established and one attribute of an entity is chosen as a primary key which gives uniqueness to each data stored in that table. A conceptual model is basically ER diagram. 2. Logical Design Now the conceptual model is converted into logical schema which is favorable data model of database system. It is more detailed than ER diagram as the entity is converted into table and attribute of an entity is converted into column of a table. In this phase only, the data type is set to Avinna Shrestha (HND / First Semester)
columns specifying the length of it. Also, if primary and foreign key are not assigned in previous phase then it is defined here.
3. Normalization Normalization is the last part of logical design. It is the process of removing any kind of redundancy and potential update anomalies present in the logical design. Each normalization phase adds more relations into the database. 4. Physical Design This is the final phase of database design, in which database is constructed with the help of Database Management System (DBMS) software’s. The SQL queries to create the database are written. The indexes, the integrity constraints and the users’ access rights are defined. And finally, it is sent to testing. Database Design tools: Database design tools are the tools that is used to design the database model before implementing it into actual database. There are lots of tools to design database, commonly used tools are as follows: Edraw Max: Edraw Max is a versatile graphics software or 2D business technical drawing software which helps to create flowcharts, network diagram, database design, business charts, engineering diagrams, floor plan, and so on. It is available in two versions Free Viewer Version and Professional Editable Version. In Free Viewer Version, we cannot save a diagram that we draw in order to save a file we have to upgrade to the professional editable version by paying some money. One of the drawbacks of this software is that once we save a diagram we cannot edit the diagram if the client requirement changes. Draw.io: Avinna Shrestha (HND / First Semester)
oriented analysis and design. The object-oriented analysis was complex and all the modelers cannot use it. Then, came the introduction of UML which diagrams were user-friendly and can be used by any people in any sector. In this task, I have used mainly two types of UML techniques which are: Entity-Relationship Diagram (ERD): An Entity-Relationship Diagram or ERD is the diagram which shows relationships of entity sets stored in a database. In other words, ER diagrams illustrate the logical structure of a database. At a first glance, it may seem like a flowchart but it contains specialized symbols that make it unique. Common Symbols used in ER diagram: Entity: Entity is represented by a rectangle. It is a component or object about which you want to store information about. It must be written in singular form. Action: It is represented by a diamond shape, which shows how two entities share information with each other in a database. It must be a verb. In some cases, one single entity can be self-linked which is called a recursive relationship. For example, the Facebook user can be friend with another Facebook user. Connecting lines: It is a line which is used to connect two entities to show the relationship between them in a diagram. Avinna Shrestha (HND / First Semester) relationship entity friends user
Attribute: It is represented by ovals, which stores information about the entity. A key or primary key attribute is unique information about an entity which is underlined. For example, a book has a unique ISBN number which uniquely identifies the book. Cardinality: It is the relationship that entity makes with one another with respect to the number of occurrences of the entity. Ordinality is also closely linked with cardinality. Ordinality determines the dependency of an entity with another entity as mandatory or optional. There are many types of notation styles that express cardinality such as: Crow’s Foot notation: fig.: Crow’s foot notation Chen’s notation: fig.: Chen’s notation Avinna Shrestha (HND / First Semester) attribute
In the above figure, I have shown the database schema of the NAC Database Management System. There are altogether eight tables or entities which encloses the data types and attributes. In database schema, entities are written in plural form. All tables have their own primary key which uniquely identifies the table. Teams table have a maximum relationship (one (and only one) to zero or many) that is three with team_fixtures, team_competitions, and players with the help of a foreign key in each table. One table admins do not have any relationship with any other tables. Data Dictionary: A data dictionary is a dictionary of data-types used in data objects or items of the database schema for benefit of programmers and others who need to refer to them in a descriptive way. After giving the name data objects or items, the type of data is described, its length is given, key (Primary or Foreign) is described, and a short textual description is provided. A collection of this information can be referenced as the data dictionary. The data dictionary of Database Management System is shown below: Admins: Table name Attribute Data type Null Length Constraint Description admins id INT NO 11 PRIMARY KEY, UNSIGNED AUTO_INCREMENT Admin id first_name VARCHAR NO 50 - First name of Admin middle_name VARCHAR YES 50 - last_name VARCHAR NO 50 - Last name of Admin dob DATE NO - - Date of Birth of Admin address VARCHAR YES - - Address of Admin Avinna Shrestha (HND / First Semester)
username VARCHAR NO 100 - Username of Admin password VARCHAR NO 100 - Password of Admin Competitions: Table name Attribute Data type Null Length Constraint Description competitions id INT NO 11 PRIMARY KEY, UNSIGNED AUTO_INCREME NT
name VARCHAR NO 100 - Name of Competition start_date DATE NO - - Starting date of competition end_date DATE YES - - Ending date of competition logo_path TEXT NO - - Logo of a Competition awards TEXT YES - Awards of a Competition for winning team Teams: Table name Attribute Data type Null Length Constraint Descriptio n teams id INT NO 11 PRIMARY KEY, UNSIGNED AUTO_INCREM ENT Team id name VARCHAR NO 100 - Name of a Avinna Shrestha (HND / First Semester)
Players: Table name Attribute Data type Null Length Constraint Description players id INT NO 11 PRIMARY KEY, UNSIGNED AUTO_INCREMENT Player id first_name VARCHAR NO 50 - First name of the player middle_name VARCHAR YES 50 - last_name VARCHAR NO 50 - Last name of the player age INT YES - - Age of player salary INT YES - - Salary of player contact VARCHAR YES 10 - Contact of player team_id INT NO 11 FOREIGN KEY, UNSIGNED Primary key of team Player_positions: Table name Attribute Data type Null Length Constraint Description player_positions id INT NO 11 PRIMARY KEY, UNSIGNED, AUTO_INCREMETN
position VARCHAR NO 50 - The position of a player Fixtures: Avinna Shrestha (HND / First Semester)
Table name Attribute Data type Null Length Constraint Description fixtures id INT NO 11 PRIMARY KEY, UNSIGNED AUTO INCREMENT Fixture id date DATE YES - - Fixture date start_time TIME YES - - Starting time of fixture end_time TIME YES - - Ending time of fixture stadium VARCHAR YES 100 - Fixture of stadium Team_fixtures: Table name Attribute Data type Null Length Constraint Description team_fixtures id INT NO 11 PRIMARY KEY, UNSIGNED AUTO_INCREMENT
team_id INT NO 11 FOREIGN KEY, UNSIGNED Primary key of teams table fixture_id INT NO 11 FOREIGN KEY, UNSIGNED Primary key of a fixtures table Avinna Shrestha (HND / First Semester)
P2:
Database: Database is an organized collection of data or information for the quick access and retrieval by a computer. As a certain data will be changing constantly, the database allows to modify, add, and delete data stored in it. A database is a file or set of files which is stored on some secondary storage media. A database can be broken into one or more tables. Each table stores their own set of data. In the table, there are two entities horizontal/row and vertical/columns (entity name as described in database design). Columns specify what kind of information/data is to be saved in a table and have its own set of data types. Whereas, the row represents the actual data. Data are also organized into tables which determines the relationship between different columns. In each table, there should be one primary key which gives uniqueness to each data in the table. Figure alongside is from phpMyAdmin, an open-source software for storing, modifying the database. We can see a set of databases assembling their own data in the left-side of the figure. When we click on ‘nac_sports’ database, for example, a new tab opens breaking the database into multiple fields/tables. Going forward, if we click on any one of the tables then another page opens showing the structure (Columns name) of the table and data stored in the table. Each table has a column which is the primary key to give a unique identity to the data. At last, database is collection of data stored in some storage device which users can easily access, search, manipulate, group, and delete the records using certain commands. Nowadays, database is not only limited to large organization instead it is used by a single person to create his/her own database such as movie collections, personal time-table, shoe collection etc. and a small organization like school to save the students as well as teachers information. Database Management System (DBMS): Avinna Shrestha (HND / First Semester)
Database Management System (DBMS) is the software which is used to create, manipulate, and manage the database. DBMS provides us with a tool, which allows performing a various task such as creating a database, creating tables, adding data in it, updating data in it, altering tables, views as well as delete data of the database and many more by making sure that data is organized properly and data is easily accessible. DBMS mainly act as a bridge between the database and end users. Examples of DBMS are MySQL, SQL Server, Oracle, dBASE, FoxPro etc. In this task, I will be using MySQL Workbench to create a database. Some of the characteristics of DBMS are as follows:
- DBMS allows users to store any kind of data.
- It supports ACID (Accuracy, Completeness, Isolation, and Durability) properties which makes sure that any kind of data is not deleted while performing SQL queries such as insert, update, delete and preserves data consistency.
- It allows users to show the complex relationship between the data making it more meaningful which makes the data easily understandable to other users.
- Data is one of the crucial things in the earth so, the certain measure should be taken for the protection of data. Data integrity feature of DBMS prevents the unauthorized access of database, making it secure.
- Data is stored in some kind of hardware which might crash anytime. So, at that time backup is very necessary. This feature is provided by DBMS to keep a backup of data and recover it whenever needed.
- Single data can be accessed by many users residing in a different location at the same time, which requires the database system to alter simultaneously. At that point, DBMS allows them to simultaneously use the database without any error. There are mainly three types of users in DBMS who have their own set of rights, clearance, roles which provides a working interface to the DBMS. These users are: Database Administrator: They are the one who is responsible for maintaining the database security, making policies, strategies and providing technical support. Application programmers: They are the one who writes programs to create a user-friendly software that uses the database. It is a bridge which provides access to the database for end users. End Users: They are those who access the database. They just use the application program and does not have any information about what is running behind the scenes. Structural Query Language (SQL): Avinna Shrestha (HND / First Semester)
identifies the data in a table. For the creation of this Database Management System (DBMS), I used MySQL in order to create database, tables and to establish a link between each table. And, I used phpMyAdmin to review and execute the database (nac_sports) constructed in MySQL. Fig.: Creating a database named ‘nac_sports’ Fig.: Using database ‘nac_sports’ to execute other queries Fig.: Tables in nac_sports database Here are the descriptions of the table created in the ‘nac_sports’ database: Avinna Shrestha (HND / First Semester)
Admins: Admins table is created to store the details of a user. It stores id, name, address, date of birth, username, password of an admin. In order to store the information of an admin, admins table have eight attributes which are id, first_name, middle_name, last_name, dob, address, username, and password. Most of the attribute uses datatype VARCHAR except id and dob which uses INT and DATE respectively. Among which ‘id’ is the primary key which uniquely defines each admin. This is the only table in a database which is not linked with any other tables. Here is the image of how admins table is constructed in MySQL and structure of admins table in phpMyAdmin: Fig.: Construction of admin table in MySQL Workbench Avinna Shrestha (HND / First Semester)
Teams: Teams table is created in order to store the details about the team. It has id, name, location, contact, formation_year, div_type, logo_path attributes which stores unique id, name, address, team formation year, division type of a team, and logo of a team respectively. ‘id’ attribute is a primary key in a table which helps to uniquely identify the team. It has a relationship with three tables ‘team_competitions’, ‘team_fixtures’, ‘players’ in which it is a parent table. Here is the image of how teams table is constructed in MySQL and structure of teams table in phpMyAdmin: Fig.: Construction of teams table in MySQL Workbench Fig.: Structure of teams table in phpMyAmin Avinna Shrestha (HND / First Semester)
Player_positions: Player_positions table is built to store the positions of players. It only stores two information id and name of a position in id and position attributes respectively. This table has one primary key ‘id’ which is linked with players table and it is parent table of players table. Here is the image of how player_positions table is constructed in MySQL and structure of a player_positions table in phpMyAdmin: Fig.: Construction of player_positions table in MySQL Workbench Fig.: Structure of player_positions table in phpMyAdmin Avinna Shrestha (HND / First Semester)
