Database Management System

Connie G Farris

Colorado Technical University

Advanced Database Systems

(CS352-1804A-01)

Jeffrey Karlberg

10/17/2018

Database Systems Project

Table of Contents Database Systems Project 1 Project Outline 3 A description of the 3-level ANSI architecture model 4 The difference in responsibility between Data administrator and Database administrator 6 ERD Screenshot 7 The need for enhanced diagram tools 8 Multiplicity in Entity Relationships 9 The normalization of a given logical data model to Boyce-Codd Normal Form 11 References: 19

Data storage is a complex affair. Data is stored in form of bits where there are different levels of architecture involved. The following are levels of architecture used in data storage. The external level is the top level in the architecture of the database management system. It is the level in which end users access data. The data in this level is simple as the end user does not need to understand data complexity. Data in the external level is viewed separately by users depending on their access rights. Conceptual level is the middle level is the database architecture. It the level that determines what data can be stored in the database. The conceptual level also defines the relationship between the stored data. This level of the database is managed by the database administrator (Kroenke, Auer, Vandenberg & Yoder, 2018). Internal level defines how data is stored on the secondary storage devices. This data is organized in form of folders and files. The internal level is tasked with providing the storage spaces needed to store data

A description of data independence

Data independence is the ability to make changes in one level of the database without making changes in the other levels. Databases with levels or layers make data independence possible. In databases that are not layered, any changes made affect the entire database and thus data independence cannot be achieved. Data independence could also be defined as the separation of data and the applications that process it. There are two types of data dependence; logical and physical data independence. Logical data independence is the process of modifying data patterns without affecting the programs that they run on. It refers to changes made in the conceptual level of the database (Mullins, 2012). These changes do not affect the view of data at the external level. Logical data independence is relatively difficult to achieve. Physical data independence is the process of making changes in the internal level of the database without these changes affecting the other levels in the database. Physical data independence is easy to achieve as it mainly involves changing the file structure organization in the secondary storage.

The duties of a data administrator (DA) and a database administrator (DBA) are diverse. A DA controls and organizes data in an organization. They also determine which data needs to be stored in the database according to the needs of the organization. The DA is in most cases a senior employee in the organization with management expertise and is involved in making critical decisions that affect the organization. The DBA is mainly tasked with the design and implementation of the organization’s databases (Foster & Godbole, 2014). The DBA offers technical support on the use and design of the databases. A DBA only offers technical services and are not necessarily senior employees in the organization. They do not have management skills and they are not involved in the decision-making process of the organization.

In most organizations, the DA and DBA are two separate roles and are performed by two different people. Some organizations combine the two roles where the DA and DBA duties are performed by one person. Separating the two roles is important because the DBA is only skilled in designing and managing databases and they may find it difficult data organization. Data organization is crucial as it defines which data will be stored in databases and who has the permission to access it (Brathwaite, 2007). Some of the data in an organization are too sensitive to be stored in a database and so it is the duty of the DA to implement other storage methods. In cases where the DA and the DBA is one person, there is an advantage of cost reduction as only one salary package is paid instead of two.

Enhanced diagram tools provide and improve representational abilities of extensions to the ER diagram. The resulting diagram is referred to as the enhanced ER diagram. The diagram represents the concept of a subclass as well as its superclass and the associated process of attribute relationship or relationship inheritance. Occasionally it becomes essential to create extra attributes of entities, either due to extra definite attributes or because of definite relationship kinds.

Enhanced diagram tools allow the creation of an Effective EERD. An elegant EERD will aid the database developer to build strong and useful database systems. The tools allow the database designer to evaluate the entity relationship diagram to ensure that the modeling of the system design meets the requirements of the business. A well designed EERD is stable in that it can support changing business needs, has breadth by calling all data needed to be stored and structured in the model and is flexible by allowing data in the model to be restructured to adapt to changing requirements. Enhanced diagram tools ensure efficiency by modeling data using the right symbols. The tools enable the system design creators and users effortlessly understand the EER and allow room for conformity where the model integrates smoothly with the current database structure.

There are two main processes of describing the supertype/subtype hierarchies and lattices a process known as the specialization and generalization respectively. The enhanced diagram tools show these new constructs. The tools also show the various kinds of constraints that specialization and generalization adopt. There exist two kinds of constraints namely total/partial and disjoint/overlapping. The enhanced diagram tools provide an overview and summary of the kinds of abstract data representation models like identification, specialization, and generalization (Elmasri, 2017).

Enhanced ERD diagrams incorporate Supertypes and Subtypes. A Supertype refers to an entity type that has a relationship to a single or multiple subtype. The subtype is a subcategory of entities with distinctive attributes. For example, Employee is a supertype entity while internal role and External role entities are the subtypes. Also, the Customer, Supplier and the Employee entities each have contacts as the subtype entity. This kind of relationship adopts inheritance; a model where the subtype entities get the values of all supertype attributes. This implies that the subtype instances are similarly categorized as supertype instances.

Constraints

During data modeling, it is crucial to determine whether a supertype instance may concurrently be a member of two or multiple subtypes. This is referred to as Disjointness constraints. The disjoint rule makes supertype instance to contain disjoint sets of entities while the overlap rule makes a supertype instance contain overlapping sets of entities. A supertype instance is also required to be a member of at least one subtype. This is the completeness constraints where the total specialization rule requires that each entity in the supertype belong to some subtype. In the ordinary ERD complete specialization is depicted with a double line connecting different entities while the partial specialization rule enables an entity to not necessarily be a member of any of the subtype instance.

There exist four kinds of multiplicities namely one-to-one, one-to-many, many-to-one, and many-to-many. In One-to-one entity relationships, each entity is associated with a single instance of a different entity. For instance, a customer will have a single instance as a supplier and an employee, customer or supplier instance each relates to a single contact instance. Also, an employee can either be regarded as customer interfacing or internal support implying that an employee belongs to a single employee type entity/instance (Satzinger, Jackson & Burd, 2016).

In one-to-many entity relationship, an entity instance is associated with numerous instances of the various entities. A product sales order, for instance, can have numerous order items. This means that placing an order have a one-to-many relationship with Order Line. This is also evident in the employee-order relationship where an employee can retrieve many orders

In Many-to-one entity relationship, several instances of an entity can have an association with a single instance of a different entity. This kind of multiplicity is just the reverse of a one-to-many relationship. For instance, from the Order Line relationship with the Order entity, the Order Line has a many-to-one relationship with the order entity (from the Order Line perspective).

In Many-to-many entity relationship, the entity instances can have an association with several to instances of each other. For instance, in the inventory, each product has many suppliers, and every supply may supplier numerous products. Thus, in a supply application, inventory and Supplier would have a many-to-many relationship.

First Normalization:

There are no composite attributes and every attribute is single and describes one property. So the following relations are in first normal form.

Charity (CharityID, CharityName, CharityLocation, POCName, POCID, TelExtn,CustomerID,CustomerName, DateContributionStarted, NoofMonth, DatePlaces, ExpectedContributionEnd)

Second Normalization:

If the relations are in 1NF and non-key attributes are functionally dependent on the key attribute(s) or on part of a composite key, 2NF is required.

DateContributionStarted, NoofMonth, DatePlaces and ExpectedContributionEnd depands upon CharityID, so anew table can be created:

Charity (CharityID, DateContributionStarted, NoofMonth, DatePlaces and ExpectedContributionEnd, CharityName, CharityLocation )

POC details are associated with customers so a new table can be created:

CustomerPOC(CharityID , POCName, POCID, TelExtn,CustomerID,CustomerName)

Relations after 2 NF:

CharityID(CharityID, DateContributionStarted, NoofMonth, DatePlaces and ExpectedContributionEnd, CharityName, CharityLocation )

CustomerPOC(CharityID , POCName, POCID, TelExtn,CustomerID,CustomerName)

Third Normalization:

If the non-key attributes are not functionally dependent only on key attribute(s) and if two non-key attributes are functionally dependent on each other, 3NF is required

In CustomerPOC relation, POCID depands upon CustomerID, so a new table is required

Relations after 3 NF:

Charity (CharityID, DateContributionStarted, NoofMonth, DatePlaces and ExpectedContributionEnd , CharityName, CharityLocation)

CustomerPOC(CustomerID,CustomerName)

POC(POCName, POCID, TelExtn,)

CustomerPOC(POCID CustomerID)

BCNF:

A table is considered in BCNF (Boyce-Codd Normal Form) if it’s already in 3NF AND doesn’t contain any nontrivial functional dependencies. That is, it doesn’t contain any field (other than the primary key) that can determine the value of another field

In relation charity, charitylocation depands upon charityName. CharityName is not a primary key. So BCNF is required

Relations after BCNF:

Charity (id, CharityID, DateContributionStarted, NoofMonth, DatePlaces and ExpectedContributionEnd )

CharityNameLocation(CharityID, CharityName, CharityLocation)

CustomerPOC(CustomerID,CustomerName)

POC(POCName, POCID, TelExtn,)

CustomerPOC(POCID CustomerID)

There are two basic requirements for a database to be in third normal form:

Already meet the requirements of both 1NF and 2NF

Remove columns that are not fully dependent upon the primary key.

Here all tables have the columns which fully depend on the primary key column. There is no any dependency between columns with the tables so following tables are in third normal form.

CREATE TABLE AddressDetail ( ID, Address ,City, State, Zip)

CREATE TABLE Customer (CustomerID, CustomerDetailID,Name ,Phone )

CREATE TABLE EmployeeType ( EmployeeType ,Description )

CREATE TABLE Employee (EmployeeID, EmployeeType ,EmployeeFirstName ,EmployeeLastName ,addressid )

CREATE TABLE CustomerInterfacingEmployeeDetails ( EmployeeDetailID , ProductSpecialty , HoursOfTraining, CommissionRate , CustomerID )

CREATE TABLE InternalSupportEmployeeDetails (EmployeeDetailID ,Salary, SupportArea )

CREATE TABLE Product (ProductID ,ProductName ,QtyOnHand ,Cost )

CREATE TABLE TransactionDetails (TransactionListID , ProductID , Quantity ,Price )

CREATE TABLE TransactionListType (TransactionListType ,Description )

CREATE TABLE TransactionList ( TransactionListID , TransactionListType, CustomerID , TransactionListDate, Total, ResaleFlag )

Modified ERD:

Brathwaite, H. L. (2007). Data management: Selected topics of data administration. New York: Wiley.

Elmasri, R. (2017). Fundamentals of database systems. Harlow: Pearson Education Limited

Foster, E. C., & Godbole, S. (2014). Database systems: A pragmatic approach.

Kroenke, C. T., Auer, P. J., Vandenberg, F. L., & Yoder, M. T. (2018). Database dispensation: Fundamentals, design, and execution.

Mullins, C. (2012). Database administration: The lead to practices and procedures. Boston: Addison-Wesley.

Satzinger, J. W., Jackson, R. B., & Burd, S. D. (2016). Systems analysis and design in a changing world. Boston, MA: CENGAGE Learning