Database and analytical principles PDF
35 Pages5004 Words498 Views
Added on 2020-10-05
Database and analytical principles PDF
Added on 2020-10-05
ShareRelated Documents
Database and analytical principles
TABLE OF CONTENTSINTRODUCTION...........................................................................................................................3SESSION -1 DATABASE...............................................................................................................3TASK 1............................................................................................................................................3Design of logical model of Entity relationship of Auto sales database ......................................32. Produce a scripts by using SQL command to create set of database tables.............................93. By using SQL statements, populate the tables in the database that create 10 records in eachtable............................................................................................................................................14Data Manipulation Language (DML)........................................................................................214. Discuss about the strength and weakness of top down and bottom up approach in design. .24SECTION 2- DATA WAREHOUSING.......................................................................................25TASK 2..........................................................................................................................................25Designing star schema................................................................................................................25SECTION 3- DATA ANALYTICS...............................................................................................27TASK 3..........................................................................................................................................27Car Manufacturer which contains most quantity of car models................................................27Average fuel economy for the city and highway driving...........................................................27Good and bad average fuel economy cars..................................................................................28Car manufacturer which have 4-wheel drive and 2 wheel drive with engine power more than3.5...............................................................................................................................................29Critical reflection.......................................................................................................................31RECOMMENDATION.................................................................................................................34CONCLUSION..............................................................................................................................34REFERENCES..............................................................................................................................35
INTRODUCTIONDatabase is a collection of element that lives for a long terms. It is a type of software thatprovide an interface to the database for storage of information and retrieval. This report willdiscuss about the Entity relationship diagram on the basis of their modelling tools and alsorepresenting the specific entities, attributes and their relationship between them. Furthermore,this assignment will discuss about the SQL command to create table by using My SQL relationaldatabase and it should represent the different level of table. It will critical analyse the strengthand weakness of top down or bottom up approach to design, develop the database within properjustifications. Afterwards, this assignment will describe to design a star schema of auto sale andalso make a reasonable assumption that support the design. At last, this report will describe thedata analytics tool to finding the results and outcomes. SESSION -1 DATABASETASK 1Design of logical model of Entity relationship of Auto sales database Illustration 1: ER Diagram
Entity relationship diagram is a basically a graphical representation of information system thatdepict the relationship among the entities, objects, concepts and events within system (Ghoshand Bashar, 2018). It is based on the data modelling technique that can help to define theenterprise process for designing a relational database. It involves ERD to provide the visualstarting point of database design that support to determine the information system requirementthrough an organization. In Above diagram, there are different type of entities represented such as car, seller andbuyer etc. Each entities has been shown their attributes in the form of tables. For example- In car table, there are such attributes Mileage, year, body, type and engine etc. Data Dictionary -Entities attributesBuyer_infoBuyer_id (PK)B_L_nameB_contactB_F_nameB_M_namePostcode_id (FK)Buy_sell_historyH_id (PK)attributesPurchase_datesold_datesold_pricecar_id (FK)buyer_id (FK)CarCar_id (PK)car_namecompany_id (FK)car_companycompany_idcompany_namecar_detailDetail_id (PK)
engine_sizeCo2 Emissionpricebody_typeMileagecolor_id (FK)model_id (FK)seller_id (FK)fuel_id (FK)year_id (FK)Citycity_id (PK)city_namecountry_id (FK)colorcolor_id (PK)colorcountrycountry_id (PK)countryFuel_typeFuel_idFuel_typeModelModel_idModel_namePostcodepostcode_id (PK)postcodecity_id (FK)Seller_infoseller_id (PK)S_contactS_M_nameS_F_namepostcode_id (FK)YearYear_id (PK)year
Table -1This above table is representing all the entities that has some specific attributes and also create arelationship between them. According to scenario, each table contain a unique key that help tocreate a relationship between two or more tables. In buyer_info table:Buyer_id is a primary key that help to create a relationship with postcode table becausebuyer store the postcode details in it. it has created a relational between buyer table and postcodetable. It is representing as a Foreign key in postcode_id . buyer_info and postcode has to createone to one cardinality participation. It occurs the total participation between the postcode andbuyer tables. Buyer_info table:Primary key - Buyer_ idForeign key- postcode_id In car table:car_id is a primary key that help to create a relationship with car_company table becausecompany has stored the information of car so that it has created a relational between car andcar_company table. company_id is representing as a foreign key. Buyer and car has to create oneto one cardinality participation. It occurs the total participation between the entities. car table:Primary key – car_idForeign key – company_id
In seller_info table:Seller id is a primary key that help to create a relationship with seller table and postcodebecause seller store the information of postcode, they want to sell the car so that it has created arelational between seller and postcode. On the other hand, Postcode_id is representing as aforeign key seller_info table. Seller and buyer has to create one to one cardinality participation. Itoccurs the total participation between the seller and buyer. Seller_info table:Primary key – Seller_id Foreign key – Postcode_idIn car_details table:detail_id is a primary key that help to create a relationship with car_detail table andcolour table because it store the information about the car colour in database system. colour_id isrepresenting as a foreign key in car_detail table. Car_detail and color has to create one to onecardinality participation. It occurs the total participation between the car_detail and color table. Car_details table:Primary key - detail_idForeign key- color_id In car_details table contain the information about the model, year, fuel type and other information so that it has required to use the foreign key in car_detail table to create a relationship between them. Foreign key : model_id , fuel_id , year_id etc. Each key contain the specific values in the database system in proper manner.
Justification for using ER Diagram- Designer choose the Entity relationship design because it helps to analyse and understandevery attribute in each table. It supports to identify that primary key has performed significantrole in the database system. It is also creating a relationship between the different tables. In myopinion, it allows for designer to show the relationship between the group of data and gives asimple yet an effective overview of different entities in the system. It also uses ER model tosummarised that what entities are needed in the relational database system. In this way, it can berepresenting the relationship among the records in the tables (Roy-Hubara, Rokach and Shoval,2018). It can use in the both as a reference for the designer when it has setting the foreign keyand as a means of determine the many to one, many to many relationships, which can resolve inproper manner. ER model is produced because it records the design of database that need to part oftechnical manner. In the future, it will require the maintenance of database, which necessary. Attime, ER model plays important role to easily find the entities and attributes. It also helps to thedesigner to check the overall working system of relational DBMS. Entity relationship diagram isclearly representing an appropriate flow of information which required for any type of databasedevelopment. It has to be provide the proper guidance to understand the concept of development.It can store all information or data in the form of tables to generate a specific ouput in propermanner.
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Database and Analytics Principleslg...
|28
|3733
|30
Database and Analytics (Database and Analytics) for the Auto Sale'slg...
|33
|5085
|217
Entity Relationship Diagram for the Case Studylg...
|11
|669
|409
Database Design for Universal Sport Companylg...
|18
|1222
|75
Entity Relationship Diagram for Database INTRODUCTION 3 TASKSlg...
|20
|1532
|227
Database Management System : Doclg...
|22
|1217
|96