This document provides information on Database Management System including background, requirements, mission statement, DBMS selection, ERD, normalized schema, database schema, foreign key constraints, SQL statements, group report, challenges and solutions, and bibliography.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
1 DATABASE MANAGEMENT SYSTEM TableofContents Background.........................................................2 Requirements.....................................................2 Mission statement................................................2 DBMS Selection...................................................2 ERD................................................................3 Normalised Schema..................................................3 Database schema....................................................4 Foreign Key constraints............................................4 SQL Statements.....................................................5 Group report......................................................12 Challenges and solution...........................................12 Bibliography......................................................14
2 DATABASE MANAGEMENT SYSTEM Background Requirements Thebusinessrulesrequiredfortheimplementationofthe database are described below: ï‚·Thedetailsofthepatronsaretobestoredinthesystemwith thecustomernumberbeinguniqueattributewhichistobeused for the reference of thecustomerdata. ï‚·Thedetailsofthedepartmentsaretobestoredinthe database. ï‚·Thenamesofthestoresandtheiraddressesandfaxaretobe storedinthesystemidentifieduniquelybytheirstore number. ï‚·Thedetailsoftheproductsandtheirpricesaretobestored in the system. ï‚·Thedetailsoftheordersmadebythecustomersinthesystem andemployeeshandlingtheordersarerequiredtobestoredin the system. Mission statement Thesystemwouldbehelpfulfortheorganizationin maintainingtheirdataalsoprovidetheorganizationwithefficient datastoringoptionsthatwouldbeincreasingthebusiness efficiencies of the organization. DBMS Selection TheOrganizationhaschosenphpmyadminmysqlavailableonthe xamppservertodeveloptheirdatabasethatwouldbesupporting their backend process for thedevelopmentofthesystem.
3 DATABASE MANAGEMENT SYSTEM ERD NormalisedSchema Department (DepartmentNumber(pk),DepartmentName) Store (StoreNumber (pk), StoreName,Address,Fax) Employee(EmployeeNumber(pk),LastName,FirstName,StoreNumber (fk), DepartmentNumber (fk)) Products (ProductNumber (pk),ProductTitle,UnitPrice) Orders(OrderNumber(pk),OrderQuantity,ProductNumber,OrderDate, CustomerNumber (fk), EmployeeNumber(fk)) Payroll (PayrollID (pk), EmployeeID(fk),Amount,PaymentDate)
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
4 DATABASE MANAGEMENT SYSTEM Databaseschema ForeignKeyconstraints Foreign keys for payroll table Foreign keys for Employee Table
5 DATABASE MANAGEMENT SYSTEM Foreign key for the Orders table SQLStatements Query 1 SQL Query: SelectCONCAT(LastName,"",FirstName)As'Customer Fullname', PhoneNumber,Address From Patron Order by CustomerNumberDESC;
13 DATABASE MANAGEMENT SYSTEM Therefore,thedevelopmentofthedatabasewouldbeveryusefulfor the organization in improvingtheirbusinessstrategy. Challengesandsolution Themajorchallengesfacedwasduringthedevelopmentofthe dataandworkinginthecommandpromptinterface.Themainsolution obtainedtothisproblemisthattheERdiagramwasdevelopedafter readingthecasestudythorough.TheDevelopmentoftheERdiagram wasthendonewiththehelpoftheMSVisioTool.Inadditionto this,thenormalizationofthetablestobeincludedinthedatabase wereperformed.Hence,aclearviewofthedatabasethatwastobe developed was obtained. Hence,thesolutionwasobtained. Inadditiontothis,theYouTubevideoswerereferredfor workingontheMySqlcommandpromptinterface.Thevideoshelpeda greatdealindevelopingthequeriesonthecommandprompt interface. Hence, the problemwassolved.
14 DATABASE MANAGEMENT SYSTEM Bibliography Coronel,C.andMorris,S.,2016.Databasesystems:design, implementation, & management.CengageLearning. Hilles,M.M.andNaser,S.S.A.,2017.Knowledge-basedIntelligent Tutoring System for TeachingMongoDatabase. Nascimento,L.M.E.D.,Ferreira,A.C.D.M.andGonzalez,S.D.A.,2018, March.Designanddevelopmentofageo-referenceddatabaseto radionuclidesinfood.InJournalofPhysics:ConferenceSeries (Vol. 975, No. 1, p. 012045).IOPPublishing.