Data Modelling and SQL Language : Report
31 Pages4640 Words88 Views
Added on 2020-06-04
Data Modelling and SQL Language : Report
Added on 2020-06-04
ShareRelated Documents
Data Modelling and SQLLanguage
TABLE OF CONTENTSINTRODUCTION:..........................................................................................................................1Project 1...........................................................................................................................................1TASK 1 ...........................................................................................................................................1Understanding of Database.....................................................................................................1TASK 2 : CONCEPTUAL MODELLING......................................................................................71. Possible Entities..................................................................................................................72. Designing a conceptual data model for the database of New Airlines...............................8TASK 3 : LOGICAL MODELLING...............................................................................................91. Normalisation.....................................................................................................................92. Enhanced ERD involving all normalised entities.............................................................123. Mapping of the ER model designed.................................................................................12Project 2.........................................................................................................................................15TASK 1..........................................................................................................................................15Structured query language....................................................................................................15TASK 2 : DATA MANIPULATION............................................................................................16TASK 3 : DATABASE TESTING................................................................................................171. Database testing and its importance.................................................................................172. Screenshot of the testing process on Employee database.................................................18CONCLUSION:.............................................................................................................................20REFERENCES:.............................................................................................................................21
INTRODUCTION:It is the responsibility of organisations to maintain the database of their customers. It ismaintaining all the records of each customer. This is useful in retaining clients and improvingservices. Moreover, a database is kept to store data in systematic way. Therefore, companies areable to deal with customers at right time (Storey, and Song, , 2017) Also, database providesecurity to data from been corrupt. In this case data can again be retrieved from database.Besides database is very helpful in fulfilling the demands of customers. By tracking the past dataanalyses can be made. Data model defines the logical structure of database. It shows that howdata will flow in Database management system (DBMS). Data model is of two types that isentity relationship and relational model. Moreover, Structured query language (SQL) statementsmade arranges the data in specific manner. This report will show how database is maintained inenterprise with different systems. Project 1TASK 1 Understanding of Database1) A database management system is a software that manages data in systematic way. It meansthat data can be retrieve, updated and created with the help of database. It makes it easier foremployees to read, write, delete, etc. data in quick time. Database serves as an interface betweenuser and application program (Jukic, Vrbsky,. and Nestorov,, 2016). A DBMS manages threethings:- data, database engine and database schema. The advantages of DBMS are:-Controlling redundancy- A database avoids redundancy of data. It means that multipledata can not be stored in it. This results in wastage of storage space. Integrity can be enforced- It means that data stored in database is always accurate. Thisis done by maintaining some integrity data constrains in the database. Providing backup and recovery- In any case of hardware or software failures the datacan be lost so a database always provides a backup and recovery of data.Disadvantages:- Cost- The cost of installing and maintaining a database software is very high. Also , it requiressome additional hardware components to be installed. 1
Complexity- It becomes very complex to use a database software. File based database systems is collection of application programs that access information of eachprogram or file. All files are grouped together based on their categories. Each file must be havingrelated information (Bello, and et..al., 2017). Its limitation was duplication of data andincompatibility of files. But on other hand its the advantage of file based database system are:-Back up-It provides back up of data stored in files. Editing- data can be edited using specific editing softwareSharing- It is easy to share files among multiple users at same time. Disadvantages:-Data inconsistency- It is possible that data may not be in consistent state.Data redundancy- A file based system may store same information again. It may lead todata redundancy. Limited data sharing- As data is stores in different files it allows limited sharing of databetween users or departments. Example – A payroll system is an example of top down approach in which entire details ofperson is entered. It contains a pay period object that shows per day pay and working hours ofemployees is entered in it. 2) A hierarchical data model organises data into a tree form structure. It means each record ishaving a single parent. It is very useful in describing relationships. It is having one to manyrelationships nodes with each other. 2
Characteristics of model – Its main feature is the tree like structure provides flexibility tocompany. For example- if database is made for team and its members then it becomes easy todefine parent child relationship. One to many- in this one parent can consist of many children. It becomes easy to work.Navigation- The model path is limited by predetermined structures. To access a recordquery moves in downward direction. It is easy when the location of data is known. Logical parent pointers- In this new database have to be set up for many to manyrelationships. Network model- A network model is built on hierarchical model where relationships are tiedwith many to many nodes (Balan, and et..al, 2016). It is structured in such a way that sets arerelated to each other. Each set consists of one parent record and one child. A record can be amember or child in multiple sets. Characteristics of model- It is a very simple and easily understandable model. Also, it is veryeasy to design and implement. Ease of data access- It is very easy to access the data in this model. It is much flexible thanhierarchical model.3Illustration 1: Hierarchical Data model
Data integrity- It does not allow a member to exits without a parent. A relational database model sorts data into tables known as relations. A table is having columnand row, each column contains an attribute such as price, date of birth, etc. The collection ofattributes is called domain. In this table a specific attribute is chosen as primary key that can usedin another table as foreign key (Rajakumari, and Nalini, 2014). Each row is called as tuple thatincludes data of specific entity. Characteristics of model:- Use of keys- In this each data row is given a unique key called primary key. With this data indifferent tables can be linked. Also, primary key can be used to link two rows. 4Illustration 2: Network model
Data redundancy- It avoids data redundancy as an username can only be inserted once at onelocation. This avoids same data to be stored at multiple locations.SQL- It uses SQL queries to perform the operations like storing , updating and deleting data.SQL is relatively easy to understand and allows some advanced database operations. 3) Top down approach- It is designed in for big organisation where there are many departments.In this the requirements are identified and accordingly database design is developed. Here, largenumber of entities exists and relationship between them is identified (Linoff,, 2015). It is a verycomplex database that contains a huge volume of data.Strengths:-There is full visibility of what effect it can have with any changes implemented in it. There is less redundancy of dataThe requirements are specific A high level view of all components. 5Illustration 3: Relational model
It is very easy approach for big organisation to implementWeakness:- It requires a lot of time to provide data from top to bottomIt requires a lot of communication between designer and end user database. Bottom up approach- It is the reverse of top down approach in which data is stored frombottom. It moves upwards . In this firstly data elements are identified and then grouped togetherin sets. These data elements are called attributes that are grouped to form entities. 6Illustration 4: Top down approachIllustration 5: Bottom up approach
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Data Modelling and Sql Language - Assignmentlg...
|18
|2256
|44
Big Data: NoSQL Data Model, Database Management Systems, Implementationlg...
|36
|2837
|431
Data Modeling & SQL Language: Assignmentlg...
|22
|2594
|138
Data Modelling & SQL Languagelg...
|20
|3087
|444
DBMS Principles and Applicationslg...
|8
|1379
|60
Data Modelling and SQL Languagelg...
|16
|2546
|97