Database Management Systems - Assignment Example

Business Scenario: Tyler’s Buses Name: Institution: Location: Professor: Course: Date: Introduction Tyler’s Buses is a privately owned company that runs fleet of passenger vehicles through Lancashire. Buses are travelling from Bolton city to Lancaster city. The company has three depots namely: Maintenance, Central and Training depots. The company has several employees working in different positions. The employees include general managers, inspectors, mechanics, drivers and cleaners. Mechanics are based mainly at Anderton while other few are based in Training depot. The general managers and inspectors are based in Central depot where it houses the company’s main office. The rest of the employees work flexibly. The company is composed of various types of passenger vehicles namely: Double-decker, single-decker buses, intercity coach, luxury coach and minibus. Information of the employees is stored in a database system. The table 1.1 below shows the three depots and address to each: DEPOT ADDRESS MAINTAINANCE Bolton Old road Anderton PR6 3DF CENTRAL Eaves Lane Chorley PR7 2HC TRAINING Park Lane Chorley PR7 5TC The table 1.2 below shows the bus service routes: ROUTE ROUTE NO. BEGIN END Bolton Blackpool B06 Bolton Southport B07 Bolton Preston 008 Leyland Longridge L24 Chorley Clitheroe 111 Chorley Lancaster 221 Question (a) There are several definitions that have been availed by the scholars and various people who have specialized in this field. However, no definition has clearly and solely described what this concept is all about. It is therefore us to combine the various definition and explanations to come up with a concrete understanding of this concept in database. The first definition is that a data model can be defined as a combination of concepts to clearly explain the structure of a database, the methods involved in manipulating these structures, and procedures and instructions that must be followed in a database (Watt and Navathe, 2014). Another most common definition is as a collection of concepts that describe the structure of a database.it is a good way to express data abstraction as a concept. It can also be used to make specification of the storage and making of updates. Data Model’s dynamic aspect or behavior of a database application allows the database designer to specify a set of valid operations allowed on database objects (Watt and Navathe, 2014). There are several categories of data models. The most notable ones include: High-level which are close to the way many users perceive data, the second is Low-level data models describe the details of how data is stored oncomputer storage media. The last notable one is representational data models. These models can easily be understood by end users (Elmasri and Navathe). A data model has several components. The main component include: Entities which represents real-world object or concepts being modeled, Attributes represents some property of interest in the object. For example in the object employee, the attributes could be employee ID, basic salary and job group. The third component isthe relationship among two or more entities. The relationships represent associations among various entities. The relationship expressed could be in a form of one to one, one to many or many to many (Watt and Navathe, 2014). The other notable component is Relational data model which is used most frequently in traditional commercial Database Management Systems (DBMSs). Question (b) Data modeling is considered as the first step in the process of database design. This step is sometimes considered to be a high-level and abstract design phase and is commonly regarded to asconceptual design. The purpose of this data modeling in database design is to describe: the data contained in the database for example drivers, general managers, inspectors and even vehicles for the case of Tyler’s Buses Company (Watt and Navathe, 2014). Data modeling also explains the relationships between data items for example the route that the buses go through, the location where a mechanic is based on and the driver of specific bus. It also describes the constraints on data the address for the route from Bolton to Blackpool is 006. Every bus has its route and each route has a specific address. For the next step to follow, the data items, the relationships and the constraints are all expressed using the concepts providedby the conceptual data model. Because these concepts do not include the implementation details, the result of the data modeling process is not fully formal representation of the database structure (Watt and Navathe, 2014). The third step is database design. In this step, two different tasks are involved: the first one is known as database logical design and the second one is known as database physical design. Database logical design defines the database in a data model of a specified Database Management System. Database physical design defines the internal database storage structure and the way and technique files are organized. In the database design phases, data are represented using a certain data model (Watt and Navathe, 2014). Question (c) Tyler’s Buses extended-entity relationship diagram. Entity Relationship modeling is based on two concepts:Entities, defined as tables that hold specific information and relationships, defined as the associations or interactions between entities (Watt and Navathe, 2014). So for Tyler’s Buses CompanyI am going to introduce all the entities i.e. tables and then the specific attributes it has. These will be very helpful in the creation on the entity relationship and finally showing this kind of relationship in an ER diagram. The entities that I have singled out for Tyler’s Buses Company include the following: Depots, Vehicles, Employees, Route, Mechanic, Manager, Cleaners, Driver, Stage, Inspector and Safety Training. The tables below elaborate the specific attributes of each table or entity. ENTITY ATTRIBUTES DEPOTS DepotID Dname VEHICLES RegNO Type Model Chassis EMPLOYEES EMP ID Name Salary Date hired ManREF Station ROUTES Route NO Begincity End city MECHANICS MecID Fname Lname Expert In Phone MANAGERS ManID Fname Lname CRB Phone CLEANERS Cleaner ID Fname Lname Salary Phone DRIVERS Driver ID Fname Lname Route CRB Phone STAGE StageADD SupervisorID Name Route INSPECTORS InsptID NAME CRB Phone StageADD SAFETY TRAINING Safety ID TNAME There are several types of relationships in entity relationship diagrams. The first type of relationship is one to many represented as 1: M. The second type of relationship is one to one represented in ER as 1:1 and finally many to many relationships represented as M: N. In an Entity Relationship there are also Primary and Foreign keys. A Primary Key (PK) is a unique identification of a table. It makes the table unique from the others and it is useful in the creation of relationships. The primary key is indicated in the ER model by underlining the attribute (Watt and Navathe, 2014). For example: EMPLOYEES_ EMP ID Name Salary Date hired ManREF Station EMP ID is the primary key. Also show as EMP ID (PK). A foreign key (FK) is an attribute in a table that makes referencing in the PK in a different table or can also hold nothing. The PK and FK of the table or relationship must be of different data types (Watt and Navathe, 2014). For example: INSPECTORS InsptID NAME Stage CRB Phone StageADD StageADD is a foreign key. Also shown as;StageADD (FK). The relationships seen in Tyler’s Buses Company include: Vehicles which are not in service are kept in its home depot. One depot is a home to many vehicles. This is one to many relationship. A mechanic can work in many depots. This is a one to many relationships. Employees of the company can be based in any depot. One driver is assigned to one route. This is a one to one relationship. One route can have many stage, this is a one to many relationship. One inspector supervises one stage. Many supervisors can also monitor several routes. One mechanic is associated to a one specific chassis model. Safety training can be acquired by many drivers but each driver for specific route. Cleaners can work in any of the company’s deport. This shows a many to many relationship. Tyler’s Buses Entity Relationship Diagram References Watt, A., & Eng, N. 2014. Database design (2nd ed.). S.l.: S.n. Elmasri, R., & Navathe, S. 1994. Fundamentals of database systems (2nd ed.). Redwood City, Calif.: Benjamin/Cummings Pub. Database System Concepts and Architecture (6TH ed.). (2011). PEARSON. Question two Data independence refers to the process of making data in a database system to be dependent. This actually means that we are making the data to be dependent on its own and not the programs that use them. Related to this is the concept of data abstractions. Data abstractions only reveal those data that is necessary to the user of the program. These will ensure that the complex coding and other underground actions cannot be seen by the user of the database (Watt and Navathe, 2014). From this definition we can clearly understand and conclude that data independence is a part of the data abstraction in a database. Data independence is categorized into two types. The first type is logical data independence and the second category or type is physical data type (Watt and Navathe, 2014). Physical data independence deals with hiding the details of the storage structure from user applications (Watt and Navathe, 2014, p.18). This helps the database administrator in security management of a network of computers. Writing applications where the data processed is represented in the source code of the program is possible .this helps database administrator (Watt and Navathe, 2014). Question (b) A DBA team consists of several roles and responsibilities for a large University. To manage and efficiently perform these roles and responsibilities, there must be categories for such duties. The categories for the DBA team can be as follows with their specific roles and responsibilities: The university must have a system DBA team. The team or individuals working on this are of specialization shall focus on technical issues in the university. Typical tasks center on the physical installation and performance of the DBMS software and can include the following: They will be setting and tuning system parameters concerning DBMS in the University. Other roles and responsibilities for this team include: adjusting the OS, network, and transaction processors to work with the DBMS, Making sure that appropriate storage for the DBMS is used in the university for academic, administrative and students purposes (Watt and Navathe, 2014). The team shall be responsible in ensuing that the DBMS is efficient with storage devices and storage management software without any issues (Watt and Navathe, 2014). System DBA team in the university will be communicating and interacting with other related companies. The next DBA team is the Database architect. Some of the duties and responsibilities for this team include:designing of the University data models. In case of any issues pertaining data loss in the university, this team is responsible forensuring backup and recovery measures for the databases.The other part of the team here is the data modeler. He or she is also responsible for the following tasks: ensuring that the DBAs have a sound understanding of the data models and collects data requirements for development projects. It is also the responsibility of the data modeler to analyze the data requirements(Elmasri and Navathe, 1994). Question (c) The DBA must understand that in a distributed database system, the actual database and the DBMS software are distributed from various sites that are networked. There are two types of distributed systems. Homogeneous and heterogeneous distributed database systems. An example of Homogenous; library information systems by the same University to its constituent campuses. This use of the same DBMS software allows easy data exchange between the main university and the campus.an example of heterogeneous; the various library database systems use the same machine-readable cataloguing (MARC) format to support library record data exchange (Watt and Navathe, 2014). The DBA therefore will have to focus their roles and responsibilities to ensure that data and information in database is accessed over the network efficiently and without issues. Concerns about security, speed of access and network effectiveness and efficiency must be highly prioritized here. Question (d) The work of database administrators has been reducing tremendously due to cloud automatic administration such as cloud computing. Cloud computing makes companies and organization to consume their resources as a utility instead of building and maintaining infrastructure in the house. Cloud computing has several benefits such as self service provisioning which make the end user to spin the computing resources for any workload. It makes the companies to scale up as the needs of computing are increasing. It also makes pay per use when the computer resources are being needed by the users and the workloads they use. The private cloud services are delivered to universities data Centre to internal users. This model makes universities to have versatility and convenience and at the same time preserving the management, security and control (Elmasri and Navathe, 1994). References Watt, A., & Eng, N. 2014. Database design (2nd ed.). S.l.: S.n. Elmasri, R., & Navathe, S. 1994. Fundamentals of database systems (2nd ed.). Redwood City, Calif.: Benjamin/Cummings Pub. Question 3 (A) RDBMS has been widespread in its use in several companies currently. Majority of the apps being developed now in the world has embraced this type of databases. This is used in various departments ranging from business to health and accounting. It is also used in almost all the internet daily transactions and applications (Hellerstein,Stonebraker and Hamilton, 2007). A truly relational database system therefore must have tables mainly known as entities, attributes and the values. A true relational database system has several principles which are followed in order to develop good RDSs. Some of the principle is that the information should be logically represented in the tables. The data must also be accessible logically by the primary, table and the column. Null values should be treated as missing information. Metadata should be stored regularly in the data base. Data, integrity constraints, transactions and manipulation of data must be done in simple language. Data deletion, insertion, update or retrieval should be done by a single operation. The end user and batch operations are supposed to be separated from access methods and physical storage. Base tables and updates must be shown by views. Question (B) There are various factors that make NoSQL differ greatly with Relational databases. These factors include: Scalability: It is a big drawback for RDMS because it is dependent on vertical type. It is not cheap to advance the hardware in this type of scalability. Compared to NoSQL database; which are capable of storing all kind of structures of data and information(Kriha, 2015). Crash Recovery: In relational you can easily recover crash and therefore loss of important data and information cannot be experienced. To recover data and information in NoSQL requires making backups. It is therefore not that easy(Stonebraker and Madden and Abadi, 2007). Transaction reliability: Relational is characterized by its reliable nature in transactions with the aid of ACID while NoSQL which are not very reliable. This is because they depend on BASE (Satana and Puri and Ahuja, 2015). Data Model: Relational has adopted the kind of modeling whereby data is depicted in mathematical forms and design. When we compare to NoSQL, it uses modeling in such a way that methods such as graphical and documented models (Kriha, 2015). Cloud: NoSQL are the best answer to cloud for the reason that all its features are favorable for this kind of databases. The features and characteristics of relational are not so favorable for cloud(Stonebraker and Madden and Abadi, 2007). Big data handling: Relational databases cannot handle vast data efficiently. They normally distribute data and apportion them into different servers and this makes the whole process complicated. The way in which NoSQL is structured is best suit for handling vast data. All what it takes to advance storage and data retrieval has been put in measure (Raghu, 2002). Question (C) Cloud computing can be defined as: “Cloud Computing is a model for enabling convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction. ”(Kepes, 2001). Software as a Service Software as a Service (SaaS) is defined as: This software is deployed over a collection of computers that are networked together. Customers can buy a key for the app or can subscribe to use this software whenever they want it. So the customers of SaaS here pay for the app as they use it. They can use it for free if the funds they get from making advertisements or of they make a lot of sales(Kepes,2001). Platform as a Service This service type comes with the significance of SaaS in apps but the slightly difference is that PaaS is under the development of programs. It therefore ensures that web apps are made at ease and in a simple way and ensures efficient maintenance(Kepes,2001). Infrastructure as a Service This is a method of making a deliverance of cloud infrastructure such as OS, network and others to be on high demand. So this actually assists the user not to buy this requirements but source them elsewhere(Kepes,2001). References Stonebraker, Michael; Madden, Samuel; Abadi, Daniel J.; Harizopoulos, Stavros, “The end of an architectural era: (it’s time for a complete rewrite),” Proceedings of the 33rd international conference on Very large data bases, VLDB, p. 1150–1160, 2007. P. W. Kriha, “NoSQL Databases,” [Online]. Available: www.christof-strauch.de/nosqldbs.pdf. [Accessed 8 5 2015]. NishthaJatana, SahilPuri, MehakAhuja, IshitaKathuria, DishantGosain, “A Survey and Comparison of Relational and Non-Relational Database,” International Journal of Engineering Research & Technology (IJERT), vol. I, no. 6, 2012. “NoSQL databases,” [Online]. Available: nosql-database.org. [Accessed 85 2015]. J. G. Raghu Ramakrishnan, Database Management Systems, McGraw-Hill, 2002. “NoSQL databases,” [Online]. Available: nosql-database.org. [Accessed 85 2015]. Kepes. Ben. Understanding the Cloud Computing Stack: PaaS, SaaS, IaaS.Ucloud.2011. Read More