What is Relational Database Management System?
A relational database management system is nothing but a collection of programs and software for managing huge amount of data by maintaining the data integrity and redundancy in the database. It stores the database in a tabular approach using columns and rows. The columns are called attributes and rows are called tuples in the database. It uses the rows wise approach to save the data. Apart from the data, it can manage multiple users, large number of data, optimized query for instant information retrieval. It follows the ACID property to ensure the data integrity. ACID stands for Atomicity, Consistency, Isolation and Durability of the data.
There are many relational database management systems (RDBMS) are available in present time. Most of them are free of use. For example, MySQL and Microsoft Access are RDBMSs that are based on relational model. Both works on same model; however, both have different features, Graphic user interface and some different syntaxes. However, most of the application and operations are the same.
In application architecture, it is important to design technologies on different levels to work efficiently. One of them are the 3 tier, Client-server architecture that are based on presentation, application and data layer. Presentation layer mostly shows the information that a user can see on a system or application. Application layer has all the logics and algorithms to operate a functionality of features in the system that are initiated by the users. Lastly the Data layer has all the information stored regarding the business. For example, RDBMS can be used in the data layer in a 3 Tier client server architecture. DBMS will constantly contact with the application layer. Whatever the data will be sent by the application layer will be stored. Similarly, whenever a data will be required, it will be fetched from the DBMS itself.
First Normal Form
As per the first normal form, a relation must have atomic values and there should not be any repetitive groups of information. As observing the record sheet for the hospital, it can be said that there are not any field that have repetitive groups. All fields maintain atomicity in terms of data. Also, each of the records should be identified uniquely hence, a primary key can be defined in the first normal form. The first normal form can be represented as the set of relations as below:
PATIENTRECORD (PatientName, PatientNumber, DateofBirth, Medication, Dose, Frequency, Route, Prescriber, DateStarted)
Second Normal Form
As per the second normal form, there should not be partial functional dependency. Also, the relation meets the criteria for the first normal form. Considering the set of relations from the first normal form and record sheet, there is partial functional dependencies that can be represented as-
PatientNumber à Frequencey, PatientNumber à Route.
Here the patient’s name can be identified from the Patient Number. Frequency is not determined by the Patient Number and Route is not determined by the Patient Number only. Hence the partial dependency exists in the relation. The frequency should be identified from the frequency code and route should be identified by the route code. In that case it can be managed by creating separate entity for the frequency and route. Hence the set of relation will get updated according to the second normal form that can be given as:
3 Tier Client Server Architecture
PATIENTRECORD (PatientName, PatientNumber [PK], DateofBirth, Medication, Dose, FrequencyCode [FK], RouteCode [FK], Prescriber, DateStarted)
FREQUENCY (FrequencyCode, FrequencyName)
ROUTE (RouteCode, RouteName)
**Note- (Entities are represented in bold, primary key as underlined and foreign key as underlined text.
Third Normal Form
As per the third normal form, the relation must be in second normal form first, then it should not have any transitive functional dependency between the tables. As per the set of relations in the second normal form, transitive functional dependency is found between the patient Number and medication type as a patient can have many medications and each medication can be used on many patients. To resolve this many to many issue between the patient and medication, an intermediate table required and medication type should have a separate table. Medication type should have a unique id as primary key. Hence the find set of relation for the database design can be given as below:
PATIENT (PatientNumber [PK], PatientName, DateofBirth)
MEDICATION (TypeID [PK], MedicationType)
FREQUENCY (FrequencyCode [PK], FrequencyName)
ROUTE (RouteCode [PK], RouteName)
PATIENTRECORD (PatientNumber [PK][FK], MedicationTypeID [PK][FK], Dose, FrequencyCode [FK], RouteCode [FK], Prescriber, DateStarted)
**Note- (Entities are represented in bold, primary key as underlined and foreign key as underlined text.
As per the relational database models, one can create logical, conceptual and physical model of the database. In conceptual model, only important aspects of the initial designs are discussed such as entity, attributes and relationship between them. The Conceptual model provided for this task only shows entity attributes and relationship. Although it shows the multiplicity of the relationship. However, it does not show any cardinality or optionality for the relationship, apart from that it also does not have any primary key or foreign key attributes highlighted.
As per the logical model, primary keys and foreign keys are highlighted properly with certain level of data normalization. In that case, proper design can be visualize using the ERD except the physical properties. In this task the conceptual model has been converted into physical model of the database. That is why it has highest level of normalization and shows the physical properties of the database such as attribute datatype and size of the data.
Figure 1: Entity Relationship Diagram
Source: Created by Author
|
Create Database Football; use Football; Create table Club ( ClubID int primary key, ClubName Varchar(50), Contact int(11)); Create table Players ( PlayerID int primary key, PlayerName varchar(100) not null, Contact int(11), Email varchar(50), ClubID int not null, Foreign key (ClubID) references Club (ClubID)); Create Table Fixtures ( MatchNumber int , TournamentName varchar(50), HomeTeamID int, HTScore int, AwayTeamID int, ATScore int, MatchDate Date, Venue varchar(50), Primary key (MatchNumber, TournamentName), Foreign key (HomeTeamID) references Club(ClubID), Foreign key (AwayTeamID) references Club(ClubID)); |
Insert Data
|
Insert into Club values (1, ‘Lion Kings’, 74665647); Insert into Club values (2, ‘Manchester United’, 45765656); Insert into Club values (3, ‘Barcelona’, 56456456); Insert into Players values (1, ‘Sam Wilson’, 6767656, ‘[email protected]‘, 2); Insert into Players values (2, ‘Henry Paul’, 5454556, ‘[email protected]‘, 1); Insert into Players values (3, ‘John Markson’, 553446, ‘[email protected]‘, 3); Insert into Players values (4, ‘Reg Peterson’, 565556, ‘[email protected]‘, 2); Insert into Players values (5, ‘Alex Hales’, 4568454, ‘[email protected]‘, 2); Insert into Players values (6, ‘Kane Saun’, 57676566, ‘[email protected]‘, 3); Insert into Players values (7, ‘Clay Jensen’, 4557744, ‘[email protected]‘, 1); |
a) Fixture Data
Following are the 5 suitable data for the fixture table. These are the dummy records that has been created according to the table structure.
|
— Task a Insert into fixtures values (1001,’ABC’, 1, 3, 3, 2, ‘2022-02-22’, ‘Barcelona’); Insert into fixtures values (1002,’XYZ’, 1, 0, 2, 1, ‘2022-02-21’, ‘Barcelona’); Insert into fixtures values (1003,’XYZ’, 2, 2, 1, 3, ‘2022-02-26’, ‘Barcelona’); Insert into fixtures values (1004,’ABC’, 2, 1, 3, 0, ‘2022-02-20’, ‘Barcelona’); Insert into fixtures values (1005,’XYZ’, 3, 3, 1, 1, ‘2022-02-19’, ‘Manchester’); |
Query 1
|
— Task b (i) Select * from fixtures order by matchDate desc; |
Query 2
|
— Task b (ii) Select h.clubname as hometeam, a.clubname as awayteam, f.venue, f.matchdate from fixtures f, club h, club a where a.clubid=f.awayteamid and h.clubid=f.hometeamid order by h.clubname asc, a.clubname asc; |
|
— Task c ALTER TABLE players ADD DOB Date; |
|
— Task d DELIMITER // CREATE PROCEDURE GetPlayersName(IN IClubName VARCHAR(50)) BEGIN select p.playername from club c inner join players p on p.clubid=c.clubid where clubname = IClubname; END // DELIMITER ; Call GetPlayersName(‘Barcelona’); |
Currently Tournament name is the part of primary key alongside of the match number. In that case, if we update any tournament name for an existing match with same tournament will not occur and will throw an integrity error. In that case, a tournament will not be able to have more than one matches. To fix this issue, Tournament name has been removed form the primary key. Only the match number has been assigned to the primary key as per the new SQL code below.
|
— Task e Drop table fixtures; Create Table Fixtures ( MatchNumber int primary key, TournamentName varchar(50), HomeTeamID int, HTScore int, AwayTeamID int, ATScore int, MatchDate Date, Venue varchar(50), Foreign key (HomeTeamID) references Club(ClubID), Foreign key (AwayTeamID) references Club(ClubID)); |
Beaulieu, A., 2020. Learning SQL: Generate, manipulate, and retrieve data. O’Reilly Media.
Bush, J., 2020. Learn SQL Database Programming: Query and manipulate databases from popular relational database servers using SQL. Packt Publishing Ltd.
Deari, R., Zenuni, X., Ajdari, J., Ismaili, F. and Raufi, B., 2018, September. Analysis and comparison of document-based databases with sql relational databases: Mongodb vs mysql. In Proceedings of the International Conference on Information Technologies (InfoTech 2018) (pp. 1-10).
Dorofeev, D. and Shestakov, S., 2018, November. 2-tier vs. 3-tier Architectures for Data Processing Software. In Proceedings of the 3rd International Conference on Applications in Information Technology (pp. 63-68).
Eckstein, J. and Schultz, B.R., 2018. Introductory relational database design for business, with Microsoft Access. John Wiley & Sons.
Fong, D. and Schurr, A., 2020. Relational database choices and design. In Information Technology for Energy Managers (pp. 255-263). River Publishers.
Frantiska, J., 2018. Entity-relationship diagrams. In Visualization Tools for Learning Environment Development (pp. 21-30). Springer, Cham.
Hussain, A. and Sharma, P.K., 2019. Deployment of Web Application in LAN based 3 Tier Architecture.
Johnson, N., Near, J.P. and Song, D., 2018. Towards practical differential privacy for SQL queries. Proceedings of the VLDB Endowment, 11(5), pp.526-539.
Kashmira, P.G.T.H. and Sumathipala, S., 2018, December. Generating entity relationship diagram from requirement specification based on nlp. In 2018 3rd International Conference on Information Technology Research (ICITR) (pp. 1-4). IEEE.
Kino, Y., 2018. Conceptual Modeling supported by Text Analysis. Procedia Computer Science, 126, pp.1387-1394.
Krogh, J.W., 2021. MySQL Concurrency [M]. Apress, Berkeley, CA, pp.01-01.
Kumar, S., 2019. A Review on Client-Server based applications and research opportunity. International Journal of Recent Scientific Research, 10(7), pp.33857-3386.
Link, S. and Prade, H., 2019. Relational database schema design for uncertain data. Information Systems, 84, pp.88-110.
Puetz, S.J., 2018. A relational database of global U–Pb ages. Geoscience Frontiers, 9(3), pp.877-891.
Setyawati, E., Wijoyo, H. and Soeharmoko, N., 2020. Relational Database Management System (RDBMS).
Taipalus, T. and Seppänen, V., 2020. SQL education: A systematic mapping study and future research agenda. ACM Transactions on Computing Education (TOCE), 20(3), pp.1-33.
Vanier, E., Shah, B. and Malepati, T., 2019. Advanced MySQL 8: Discover the full potential of MySQL and ensure high performance of your database. Packt Publishing Ltd.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
