This document provides an overview of database fundamentals, including the creation of an entity relationship diagram (ERD), normalization techniques, and the creation of a database using MS-Access. It also covers the creation of forms, queries, and reports. The document includes examples and illustrations to enhance understanding.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
Database Fundamentals
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Table of Contents Introduction.................................................................................................................................................3 Overview.................................................................................................................................................3 Creation of ERD with entities as well as relationship..............................................................................3 Conversion of ERD diagram within set of relational tables till 3NF as well as dependency diagram......5 Create of database by making use of MS-Access through usage of distinct constraints..........................8 Creation of form, queries and report......................................................................................................11 Conclusion.................................................................................................................................................13 REFERENCES..........................................................................................................................................14
Introduction Database refers to a data structure that is liable for storing organized information. Basically, it is a systematic gathering of data which stores as well as manipulates data. Entity relationship diagram illustrates relationship among entities which are stored within the database. This provides a visualized view of entities or objects through which it becomes easy to understand relationship among them. Different languages or methodologies are being utilized for manipulating data so that it can be understood in a better way. This report is based on airline firm which is headquartered in Sydney NSW and they operate their flights in Australia only. This report comprises of ERD diagrams, relational database and database will be created through the usage of Microsoft Access. In addition to this, form will be created, entries will be made within database and distinct queries will be utilized for making alterations within table. Overview Airline firm furnish their services in Australia and have 15 airplanes which varies from 10 to 90 seats. Each flight possesses unique airplane number, type, capacity along with their manufacturing company. They are being scheduled on daily basis and day for which flights are being scheduled. Furthermore, the time is also different. Database comprises of all the details of airport and customers who are opting for services that are being offered by them. Each ticket which is being offered comprises details of flights and their customers. Creation of ERD with entities as well as relationship There will be four objects or entities within this scenario and each have different attributes which differentiates them as well as formulate relationship among them. They are airplanes, flights, airport and customers. The tables are illustrated beneath with respect to this. Airplanes AttributesTypes Airplane_NumberInteger Airplane_TypeInteger Manufacturing_FirmVarchar
Flights AttributesTypes Airplane_NumberInteger Dept_AirportChar Dept_TimeInteger Arrival_AirportChar Arrival_TimeInteger Airport AttributesTypes Airport_CodeVarchar Airport_NameVarchar Airport_CityChar StateChar Customers AttributesTypes Airplane_NumberInteger Cust_IdVarchar Cust_NameChar Cust_TitleChar Cust_DOBVarchar Cust_AddressVarchar The different entities are illustrated above which possess by different attributes and on that basis there exist relationship among them. It is being represented through usage of entity relationship diagram (ERD). The structural diagram which is being used for database design,
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
comprises of symbols along with connectors that are utilized for visualization of major objects or entities and inter-relationship between them is referred to as ERD. For the given scenario and tables illustrated above ERD diagram is illustrated below: Figure1: ERD Data Representation Conversion of ERD diagram within set of relational tables till 3NF as well as dependency diagram. While developing the schema of RDBMS (relational database), one of the important aspects that need to be taken under consideration is to ensure that duplication must be minimized as much as possible. This would help in reducing storage amount for storing data along with, avoiding unnecessary conflicts of information which may arise due to multiple copies of same constraints. Therefore, in designing such database schema, Normalization technique can be used, which would help in ensuring that minimum data is duplicated. The core main concept of this technique is to segment the table into sub-tables to minimize chance of duplication. In this regard, normalization in database can be done in three ways – 1NF, 2NF and 3 NF where all
processes used to optimize the database more efficiently, for saving storage and reducing redundancies. 1NF (First Normal Form) Under this technique, each cell of a table contains only one value and each row stores only unique information that helps in avoiding repetition. From the above mention data, normalization of database can be done in following way – Table 1: Airplane NumberAirplane TypeManufacturing Address 122Dubbo Airport 231Darwin International Airport 311Brisbane Airport 410Dubbo Airport Table 2: Airplane Number Departure Airport Departure timeArrival AirportArrival time 1Darwin International Airport 1:06:24 AMBrisbane Airport2:02:34 AM 2Adelaide Airport4:05:22 AMNewcastle Airport 5:05:02 AM 3Brisbane Airport2:04:32 AMLondon Airport6:05:22 PM
Table 3: Airplane Number Airport CodeAirport nameAirport City State 1ANODarwinInternational Airport DarwinQueensland 2UTYBrisbane AirportBrisbaneNewSouth Wales 3JTEAdelaide AirportAdelaideSouth Australia Table 4: Airplane Number Customer IDCustomer name Customer Type Customer DOB Customer Address 111220Jhon SmithFirst class6/8/199814/16,Main streetroad, Brisbane 211221Jessica ClarieBusiness Class 12/8/199252, Morningside, Adelaide 211222Alice CooperEconomy Class 6/21/200121,Main Street, London Thus, tables 1 and 2 can be normalized in first form in following way – 1NF Table
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Airplane Number Departure Airport 1DarwinInternational Airport 2Adelaide Airport 3Brisbane Airport While all tables can be summarized in one form as – 1NF Table Airplane Number Customer Id 111220 211221 211222 2NF (Second Normal Form) To normalize the database in second normal form, two conditions are required to be met – table must be constructed in first normal form and primary key of table also need to be composed in one form only. Along with this, first column represents the primary key, which includes a set of columns that identifies a row uniquely. As per above discussion, many customers have same name and onboard in same flight with same destinations. Therefore, to distinguish customers, every customer given a unique id, as per above examples 1NF can be changed into 2NF in following way – Airplane Number Customer IDAirport code 111220ANO 211221UTY 211222UTY
Customer name Airplane number John Smith1 Brizell2 John Smith2 Gilchrist1 Since, this table doesn’t represent the uniqueness, therefore, broken down the above table into two tables achieve second normal form, in following way – Customer nameCustomer id John Smith1131 Brizell1123 Gilchrist1132 Airplane NumberCustomer id 11131 21123 21132 3NF (Third Normal Form) A table can be designed in 3NF if data represent in 2NF and transitive functional dependency of attribute (non-prime) on super key should be removed. To create 3NF, let take an example – Cus. IDCus_nameCus_ zipCus_stateCus_city 111John23224QeenslandBrisbane 222Gilchrist24425NewSouth Wales Sydney 333John25234SouthAdelaide
Australia 444Norji34425TasmaniaHobart In 3NF form, the above data can be represented in following manner – Cus. IDCus. NameCus. Zip 111John23224 222Gilchrist24425 333John25234 444Norji34425 Customer Zip table – Cus. ZipCus_stateCus_city 23224QeenslandBrisbane 24425New South WalesSydney 25234South AustraliaAdelaide 34425TasmaniaHobart
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Table 2: Airplane Number Departure Airport Departure timeArrival AirportArrival time 1Darwin International Airport 1:06:24 AMBrisbane Airport2:02:34 AM 2Adelaide Airport4:05:22 AMNewcastle Airport 5:05:02 AM 3Brisbane Airport2:04:32 AMLondon Airport6:05:22 PM Table 3: Airplane Number Airport CodeAirport nameAirport City State 1ANODarwinInternational Airport DarwinQueensland 2UTYBrisbane AirportBrisbaneNewSouth Wales 3JTEAdelaide AirportAdelaideSouth Australia Table 4: Airplane Number Customer IDCustomer name Customer Type Customer DOB Customer Address 111220Jhon SmithFirst class6/8/199814/16,Main streetroad, Brisbane 211221Jessica ClarieBusiness Class 12/8/199252, Morningside, Adelaide
211222Alice CooperEconomy Class 6/21/200121,Main Street, London Conclusion It has been concluded from this assessment that database management system helps in storing large amount of data more easily. Similarly, creating ERD which represent the large information aid in evaluating the inter-relationship among data and different entities in efficient way. It also helps in avoiding the repetition of entries, updating and retrieving information also.
REFERENCES Books & Journals Easttom, C. (2019).Computer security fundamentals. Pearson IT Certification. Wilke, C. O. (2019).Fundamentals of data visualization: a primer on making informative and compelling figures. O'Reilly Media. Huang,K.,Hussain,A.,Wang,Q.F.,&Zhang,R.(Eds.).(2019).DeepLearning: Fundamentals, Theory and Applications(Vol. 2). Springer.