This article discusses data models, schema, types of database management systems, database design approaches, and implementation of relational database systems for Desklib. It also provides a sample MS SQL relational database code.
COVER PAGE
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Contents Task L01.......................................................................................................................................................3 1.1 Data models and schema in data analysis.........................................................................................3 1.2 Types of database management systems..........................................................................................6 1.3 Database design approaches.............................................................................................................8 L02: Design and implementation of relational database systems...............................................................8 2.1 Design................................................................................................................................................8 2.2 Ms Sql relational database code......................................................................................................11 2.2 User interface design.......................................................................................................................13 L03: Data Manipulation and Query tools...................................................................................................14 3.1 SQL features and functionalities......................................................................................................14 3.2 Applying DML..................................................................................................................................15 3.3 Features of a query tool...................................................................................................................18 L04: Testing and documenting relational database systems.....................................................................18 4.1 Database testing methods;..............................................................................................................18 4.2 Test plan..........................................................................................................................................18 4.3 Importance of technical and user documentation...........................................................................21 4.4 Verification and validation...............................................................................................................22 4.5 Control mechanisms........................................................................................................................22 References.................................................................................................................................................22
Task L01 1.1 Data models and schema in data analysis A data model is a simplified representation of complex real world data structures. In database management systems there are different types of data models each of each has its own advantages and disadvantages. Types of data models include; Hierarchical data model Network and relational data model Entity-relationship model Relational model Schema (Conceptual, Logical and pysical schema). Hierarchical data model This type of data model is used to make a hierarchical database. A hierarchical database is designed to use one-to- many relationships for entities making the database. The database uses a tree like structure where an entity acts as a parent and other child entities linked to the parent entity are the child entities. The concept behind hierarchical database model is useful in specific types of databases but this model lacks versatility because it is limited to the hierarchical nature of the data where by an entity cannot have more than one parent node. The model has to maintain a hierarchy. An example of data that can be represented using hierarchical model is shown in the figure below; Figure1: hierarchical data model example Network data model The network data model was designed as an extension of the hierarchical data model. This model improved on the hierarchical model by allowing an entity to have more than one parent entity thus resulting to a graph like structure compared to the tree like structure of the hierarchical model. Databases designed using the network model have more relationships because of the many
relationships existing between the data. Because of the many relationships, the network model makes it easier and fast to access data. The figure below shows an example of a graph like network model. Figure2: network data model example Entity-relationship model Entity relationship data model involves designing the database by designing objects into entities and then designing the characteristics of the object into attributes. The entities are related with one another through relationships. Entity relationship diagrams are commonly used in this data model to model the database. The entities represented in the entity relationship diagram can then be turned into tables to form a relational database. A good example is designing a database for a school where a student is an object. The student object can be designed as an entity having attributes like student id, name, address and age as shown in the figure below;
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Figure3: Entity-relationship model Relational Model Relational model involves organizing data in two dimensional tables and maintaining the relationship by through a common field. E.F Codd introduced this model in 1970 and it’s the most widely used data model to date. Tables in relational model are known as relations which is the basic structure of data. The concept of normalization is widely used in relational model to achieve highly normalized relations. The figure below shows sample data in a relational model;
Figure4: Relational model sample data Schema Conceptual, logical and physical data model are used to model data. All of them contain entities, relationships but they are different in terms of their purpose and the target audience they are intended for. In general, the conceptual and logical data models are to model data in a business oriented manner while the physical data model is used by the database designer to represent the physical database structure for implementation of the database. Conceptual model is specifically used to model data gathered from business requirements. The entities and relationships in this model are designed based on the business requirements. Logical model is an advanced conceptual model that is also modelled based on the business requirements. It differs from the conceptual model because it shows columns and data types although the column type can be optional. Physical model represents the final blueprint of the relational database that will be used to implement the database in a relational database management system. This model is used by the database designer and is supposed to be followed as it is. 1.2 Types of database management systems Traditional file processing system This type of database management system was developed in an attempt to shift from the manual filing system (Thakur, 2013). It offers a method through which files can be stored and
organized thus making it easy to access the data contained in the files. Traditional file systems used storage devices like CD-ROM or a hard disk. Characteristics of the file processing system are; oData is stored as a group of files. oEach data file is independent on its own. oFlat file is the name used to describe each file. oA file contains processed information for a specific function, for example inventory or accounting. Traditional file processing system have been in use for a long time. Advantages of using traditional file processing system are; oThey are easy to use- Setting up and using traditional file processing systems is easy but on condition that the number of files in question is small (Kokemuller, 2011). oRequires minimal investment- Adapting traditional file processing systems requires minimal investment because it does not require development of complex software to manage the data. The purchase of a file processing system can be a one off small fee with minimal management and maintenance. However, traditional file processing system has presented its limitations over the years due to the diversification of data needs by organizations. Some of the limitations are; oIsolated and separated data- Making decisions involves analysis on the different files to determine which file has the right data or to relate the data between the files. oData duplication- Data is stored in multiple files most of the times. This results to unnecessary duplication of data which leads to waste of the storage space thus increasing the associated costs (Singh, 2014). oUsing traditional file processing systems presents a challenge of presenting the data from the user’s view because of the multiple isolated data files. oData security in traditional file system because the information stored in flat files is easily accessible. oConcurrency problems- access of the same data file by different users can create concurrency problems in the data if the two users are accessing data simultaneously. oTransactional problems- Traditional file based system does not meet ACID (Atomicity, Consistency, Isolation, Durability) properties. Database management system (DBMS) DBMS is a system that is designed to act as an interface between the database and the end user of the database. The end user can be a real user or an application. DBMS provides an interface through which CRUD (Create, Read, Update, and Delete) actions can be performed on a database. DBMS systems were designed to solve limitations of the traditional file system and to improve on its features. Relational Database Management System (RDBMS) is a type of DBMS that has row-based table structure connecting data elements and includes other additional functions that ensure accuracy, security, consistency and integrity of the data. Advantages of DBMS can be looked at on the basis of the limitations of the traditional file based system. These advantages are;
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
oDBMS eliminates the issue of redundant data by use of normalization. By maintaining one repository of data that can be accessed by many users there is no need to maintain multiple files for each user as it is in the case of traditional file processing system. oDBMS eliminates the issue of concurrency through normalization and other additional mechanisms that ensure there is consistency when data is accessed and modified simultaneously by more than one user (Rouse, 2015). oData integrity in DBMS is ensured as the database is high normalized thus valid relationships exist between the entities. DBMS makes it possible to enforce constraints like cascade update which ensure that data is always consistent between entities that are related. oData searching is fast in DBMS compared to the traditional file processing system because by using queries a DBMS can search large sums of data. 1.3 Database design approaches The following approaches can be used to design the database; Top-down approach- this database design approach starts from the general moving to the specific meaning that the starting point of the database design is the general idea of what is expected of the system and then determining what data has to be stored by the end users. Top down approach requires a deep understanding of the system(Studytonight.com, n.d.). Bottom up approach starts with the specific moving up to the general details of the required system. The starting point of the design process is review of reports, forms and screens then working backwards to determine what type of data will be stored in the database. Tools and techniques approach This approach involves designing the database through three phases; conceptual phase, logical phase and physical phase. In each phase a model is achieved. Conceptual phase is specifically used to model data gathered from business requirements. The entities and relationships in this model are designed based on the business requirements (Visual-paradigm.com, n.d.). Logical model is an advanced conceptual model that is also modelled based on the business requirements. It differs from the conceptual model because it shows columns and data types although the column type can be optional. Physical model represents the final blueprint of the relational database that will be used to implement the database in a relational database management system. This model is used by the database designer and is supposed to be followed as it is. L02: Design and implementation of relational database systems 2.1 Design Based on the requirements, the following conceptual model can be used to model the database.
Figure5: Conceptual ERD The conceptual ERD can be used to design a data dictionary that will be used to implement the database as follows; TableColumnData TypeNull/Not NullConstraint EmployeeEmployeeIDChar(5)Not nullPrimary key firstNameVarchar(50)Not null LastnameVarchar(50)Not null PositionVarchar(25)Not null supervisorChar(5)NullForeign key references employee (employeeID) BranchbranchCodeChar(5)Not nullPrimary key managerIntegerNot nullForeign key
2.2 Ms Sql relational database code create table employee ( employeeID char(5) primarykey, firstName varchar(50) notnull, lastname varchar(50) notnull, position varchar(25) notnull, supervisor char(5) null ); alter table employee add foreignkey(supervisor)referencesemployee (employeeID) on delete cascadeonupdatecascade; create table branch ( branchCode char(5) primarykey, manager char(5) not null, street varchar(50) not null, city varchar(25) not null, postcode integer not null, foreign key (manager) referencesemployee(employeeID)onupdate cascade on delete no action ); create table equipment ( equipmentID char(5) primarykey, name varchar(50) not null, branchCode char(5) not null, foreign key (branchCode)referencesbranch(branchCode)onupdate cascade on delete no action ); create table client ( clientID char(5) primarykey,
firstname varchar(50) notnull, lastname varchar(50) notnull, age integer not null, phone varchar(25) not null, street varchar(50) not null, city varchar(50) not null, postcode integer not null ); create table sales ( saleID char(5) primary key, clientID char(5) not null, equipmentID char(5) notnull, salesman char(5) not null, salesDate date not null, foreign key (equipmentID)referencesequipment(equipmentID), foreign key (clientID) referencesclient(clientID), foreign key (salesman) referencesemployee(employeeID)onupdate cascade on delete no action ); create table hiring ( hiringID char(5) primarykey, branchCode char(5) not null, clientID char(5) not null, equipmentID char(5) notnull, hireDate date not null, returnDate date null, salesman char(5) not null, foreign key (equipmentID)referencesequipment(equipmentID),
foreign key (clientID) referencesclient(clientID), foreign key (branchCode)referencesbranch(branchCode), foreign key (salesman) referencesemployee(employeeID)onupdate cascade on delete no action ); Successful execution of the code 2.2 User interface design Factors to consider while designing user interface; Easy to use- The user interface should be easy to learn and grasp for the end user this making it easy to use at all times. Functionality of UI- The user interface designed should be able to meets its functionality. It’s better to design a simple design that meets all the functionalities required rather than designing a complex user interface that does not meet all the requirements. Reliability- The user interface should be reliable at all times thus the designer should design it to perform as expected at all times Consistency- Consistency in the design should be maintained to make it possible for the user to switch through different elements of the user interface. The user should not feel like they have are using a new application after switching from one screen to another. Performance- The user interface is expected to perform different functionalities using the least amount of time and resources available. Menu driven- The user interface designed for the application should be easy to navigate making it easy for the user to use the whole application consistently. Button-based design- There are many rules to designing buttons but perhaps one of the most important rules is to make a button look like an actual button. This can involve adapting common button designs that make it easy for the user to recognize. Another important factor to consider when designing buttons is to put buttons where users expect them (Nielsen, 2018). Data requirements
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Figure6: Data flow diagram The data diagram shown in the figure above can be used to show the data requirements that will be critical in designing the user interface. L03: Data Manipulation and Query tools 3.1 SQL features and functionalities SQL (structured Query language) is a domain specific language. SQL was designed to manage data through RDBMS. It handles structured data that is held in a relational database. The handle operations are performed using different types of SQL which include; DDL- Data definition language- this subtype of SQL is used to define data structures in a database. DDL statements are used create, remove and modify database objects. These statements are used for database objects like tables, users or indexes. Common DDL statements include CREATE, DROP and ALTER. DML- Data Manipulation language is a subtype of SQL that is used to add, delete or modify data in a database. Examples of common DML statements include INSERT, UPDATE and DELETE. DCL (Data Control Language) is a substype of SQL that is used to control the access of data in a database. Example of a DCL command is GRANT. There are different query tools that can be used to execute SQL statements for example using terminal or using SQL server management studio. These tools make it easy to write and execute SQL statements by making it easy for the user to write and debug queries. SQL tools also provide better aggregation of data thus the data makes more sense to the user.
3.2 Applying DML Database insert queries. insert into employee (employeeID,firstname,lastname,position, supervisor) values ('E001','John','Doe','Director',null), ('E002','Arya','Stark','manager','E001'), ('E003','Cersei','Lannister','manager','E001'), ('E004','Brianne','Tarth','Engineer','E002'), ('E005','Geofrey','Baratheon','Technician','E004'), ('E006','Khal','Drogo','salesman','E005'), ('E007','Snow','White','salesman','E005'), ('E008','Parker','Peter','salesman','E005'); insert into branch (branchcode,manager,street,city,postcode)values ('B001','E002','New York dr','NewYork',1234), ('B002','E002','los Angelesdr','losAngeles',1235), ('B003','E002','Queens dr','Quees',1233), ('B004','E003','Melbourne road','Melbourne',1234), ('B005','E003','New York dr','NewYork',1234), ('B006','E003','Winteefell dr','Winterfell',1235), ('B007','E002','kings landingdr','Kingslanding',1235), ('B008','E002','New York dr','NewYork',1234); insert into equipment (equipmentID,name,branchCode)values
3.3 Features of a query tool For the database, the query tool used to perform DML was SQL Server Management Studio 2017. This tool made it easier to perform DDL and DML operations on the database. The tool provides a simplified way of writing SQL queries using a graphical user interface that is easy to use and the SQL code is executed on the background. After performing DDL and DML operations using the SQL server query tool the tool can be used to show the status of the database by showing the number of tables and their structures and showing records in a table. Database query tools can also be used to perform forward engineering of databases to export L04: Testing and documenting relational database systems. 4.1 Database testing methods; Black box testing Black box testing for a database is done without knowledge of the internal structure of the system. This means that the user does not have to know the objects making up the system and their relationships. A good example of a test is to just focus on the inputs and outputs without the need to know how the processing will take place(Goel, 2018). White box testing White box testing in database systems involves testing the internal structure of the database regarding its inputs, outputs and relationships between the objects making up the database system. For example a test can be done to test whether an input will result to a certain output. This can also include just going through the code to identify changes. Acceptance testing- Acceptance testing for database systems is done to ensure that the database system meets its requirements and operates how it is expcted to operate. For example storing a new record for an employee should save the record. 4.2 Test plan Test Description Expected outcome Actual outcome
Select top 200 rows in branch table using SQL managemen t server tool The database should select top 200 rows in the client table. Select top 200 rows in client table using SQL managemen t server tool The database should select top 200 rows in the table. Select top 200 rows in employee table using SQL managemen t server tool The database should select top 200 rows in the employee table. Get all hiring records that whose item has not been returned The database should return all records in the hiring table where the return date is null
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Change the sales date of the first record in the sales table. The sales date of the first record in the sales table should be updated to the new value Update employee Id branch manager The id of the manager should be updated and all related records in the branch should be updated too Change the equipment id of an equipment that is involved in a sale The dabatase should restrict this action because it violates this action Delete an equipment that is already in the sales table. The database should restrict the action because it violates the
foreign key Fetch records from client and sales table Join should work between the two tables Fetch records from the employees and branches table Join should work between employee s and branches table 4.3 Importance of technical and user documentation Technical and user documentation is important in a database system because it acts as a basis through which the end users can learn about the system from how to open and use the system. The user documentation can also act as point of reference users want to refer incase the user has forgotten on how the system works. The following user document can be used for the following database system: Opening the system. To open the system your system should have the following minimum requirements: Memory *Minimum: Express Editions: 512 MB All other editions: 1 GB Recommended: Express Editions: 1 GB
All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance. Processor SpeedMinimum: x64 Processor: 1.4 GHz Recommended: 2.0 GHz or faster Processor Typex64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support To open the system navigate to the sql server management studio and run the program. 4.4 Verification and validation Verification and validation involves ensuring that database is running the way its intended to. Validation and verification for the proposed database was enforced by defining the appropriate data types for each column. For example a column like sales date in the sales table should only take a date value or a value that can be parsed to a data implicitly by SQL server. Fields that take integers should only accept integers or a value that can be implicitly converted to an integer. Verification and validation was also achieved through referential constraints implemented for different relationships. Referential contraints ensure that no record is inserted in the child tables for records that do not exist in the parent table. 4.5 Control mechanisms There are various control mechanisms that are available in a database system. These mechanisms are; Users- this mechanism involves creating a user and granting rights and privileges to the user. The privileges can include execution of different DML, DDL or DLC statements. The user is only allowed to execute statements under the user he or she is assigned to. Roles- A role is a collection of a number of permissions that can be assigned to one or many users. The privileges can include execution of different DML, DDL or DLC statements. A user allocates only privileges which exist in the role that he or she is assigned to. References Goel, A. (2018). Database Testing: White Box and Black Box - Magoosh Data Science Blog. [online] Magoosh Data Science Blog. Available at: https://magoosh.com/data-science/database-testing-white- box-and-black-box/ [Accessed 4 Dec. 2018]. Kokemuller, N. (2011). Advantages & Disadvantages of Traditional File Organization. [online] Yourbusiness.azcentral.com. Available at: https://yourbusiness.azcentral.com/advantages- disadvantages-traditional-file-organization-28765.html [Accessed 4 Dec. 2018]. Nielsen, J. (2018). 10 Heuristics for User Interface Design: Article by Jakob Nielsen. [online] Nielsen Norman Group. Available at: https://www.nngroup.com/articles/ten-usability-heuristics/ [Accessed 29 Nov. 2018]. Rouse, M. (2015). What is database management system (DBMS)? - Definition from WhatIs.com. [online] SearchSQLServer. Available at: https://searchsqlserver.techtarget.com/definition/database- management-system [Accessed 4 Dec. 2018].
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Singh, C. (2014). Advantages of DBMS over file system. [online] beginnersbook.com. Available at: https://beginnersbook.com/2015/04/dbms-vs-file-system/ [Accessed 4 Dec. 2018]. Studytonight.com. (n.d.). Database Models in DBMS | Studytonight. [online] Available at: https://www.studytonight.com/dbms/database-model.php [Accessed 4 Dec. 2018]. Techopedia.com. (2014). What is a Hierarchical Database? - Definition from Techopedia. [online] Available at: https://www.techopedia.com/definition/19782/hierarchical-database [Accessed 4 Dec. 2018]. Thakur, D. (2013). Traditional File Processing System. [online] Ecomputernotes.com. Available at: http://ecomputernotes.com/fundamental/what-is-a-database/traditional-file-processing-system [Accessed 4 Dec. 2018]. Visual-paradigm.com. (n.d.). Conceptual, Logical and Physical Data Model. [online] Available at: https://www.visual-paradigm.com/support/documents/vpuserguide/3563/3564/85378_conceptual,l.ht ml [Accessed 4 Dec. 2018].