Advanced Database Systems for fedulike
ramhldokuhn
cs352_randlekuhn_ip3.docx
Advanced Database Systems CS352
Unit 3 Individual Project
Randle Kuhn
03/07/16
Contents The Database Models, Languages, and Architecture 3 Database System Development Life Cycle 6 Database Management Systems 9 Advanced SQL 16 Web and Data Warehousing and Mining in the Business World 17 References 18 The Database Models, Languages, and Architecture
It is exceedingly essential for every organization to evaluate its constituent database needs/requirements so as to determine whether it will be operationally compatible with the distinct architectural layouts available. Making the wrong choice of architectural design results to degraded database performance in terms of speed of accessing data as well as executing data definition and manipulation commands. These architectural database designs include the 3-level architecture which is implemented under the ANSI-SPARC (American National Standards Institute, Standards Planning and Requirements Committee) architectural framework of computational standards. It was inaugurated in the year 1975 as an abstract standard for utilization in DBMSs (Database Management System). The core objective of this 3-level architecture is to introduce efficient database operability by separating the users view from the other views (internal, conceptual and external). The user’s view is implemented and operates independently of the underlying database architecture. Therefore, multiple users are able to access similar data items synchronously while at the same time customizing their respective views with no regard to the other users’ views (www.computingstudents.com, 2009). Additionally, it ensures that the users are not presented with the sophisticated hardware/physical implementation details which are basically irrelevant to users. The access speed for this type of architecture is exceedingly high with fault tolerance capabilities.
Data independence refers to a very important concept utilized in centrally oriented database management systems and which incorporates data transparency. This sort of transparency exempts the users from being affected by any alterations conducted on the structural or organizational makeup of the underlying data. According to the guidelines followed by data independence policies, the user applications should not be involved in problems or issues emanating from the internal data definitions. Operations conducted by the user applications should not be influenced in any way by these internal data modifications (Zaiane, 2016). Data independence is subdivided into two categories namely first level and second level of data independence.
Data administrators are responsible of many essential roles which are different from those of a database administrator in several ways. For instance, a data administrator is in charge of coming up with the necessary definition of data items, creating names to refer to various data items as well as their respective relationships. He/she often consult database analysts in order to deliver the most suitable results. Additionally, they are concerned with presiding RDBMS (relational database management system) application installations since they possess extensive knowledge in requirements analysis and integrity of data. On the other hand, a database administrator is mainly involved in controlling information (processed data) storage and management within the database architecture. Moreover, they are concerned with evaluating and monitoring data stored in databases. Data backups are created and supervised by database administrators in order to ease data recovery operations during system crash or data loss (Tripati, 2010). They regulate and monitor database accessibility with the aim of preventing unauthorized personnel from penetrating the database security.
If the roles conducted by both database and data administrators are assigned to a single individual, the company will benefit a lot from that since the wages will greatly decrease. Additionally, the individual would enjoy higher salary as more responsibilities are presented to him/her. However, there are several disadvantages involved with this such as the fact that the individual will be bombarded with excessive work since the roles of data and database administration are too complex for a single person to handle. This will definitely cause degraded performance as well as ineffective database operability.
Database System Development Life Cycle
DATABASE MODELLING
The scenario of the organization mentioned can be implemented using access tables and ERDS. Data are put in access tables and then they are related together as one. The organization need three tables namely, customer table, supplier table and employee table. Through use of relationship the data can be interlinked together to produce better result. Primary key is a unique identifier which is used to linked data together.
Customer Table
Supplier Table
Employee Table
ENTITY RELATIONSHIP DIAGRAMS
This is a data modelling technique that is used to graphically represent information system that shows the relationship between people and objects. It act as a foundation of relational database. It help to define business process.
ENTITY RELATIONSHIP DIAGRAM
The above scenario can be illustrated using the above tables and Entity Relationship Diagram (ERD). An enhanced Entity Relationship Diagram is one that deviates from the traditional Entity Relationship Diagram. It has many features and uses several concepts that are closely related to object oriented programming. Enhanced ERD is more flexible, efficient and accessible tool than the traditional ERD.
Enhanced Entity Relationship Diagram can support features like inheritance, generalization and specialization. It is the most efficient Entity Relationship Diagram.
In conclusion, enhanced entity relationship diagram must be implemented to enhanced better data interpretation and tabulation.
Database Management Systems
Part 1
Transform into first normal form: get rid of repetitive groups for instance, there should not exist two rows/tuples which contain repeating data groups. Therefore, it should not be possible to fetch the same tuple by utilizing multiple columns. A relation in 1NF should not contain a row which has a column that maintains more than one value (usually separated with commas).
The dependency diagram in 1 NF is shown below:
Normalize into second normal form: get rid of all partial dependencies of fields on the primary key attributes. For example, the CustomerName is not fully functionally dependent on the entity’s primary keys (CustomerID and DatePlaces). CharityName, CharityLocation and POC_ID attributes have transitive dependencies on the non-prime attribute CharityID. TelExtn and POC_Name attribute are transitively dependent on the POC_ID attribute which is not a primary key. Transitive dependency will be dealt with in the third normal form. The relational schema in 2NF is shown below:
Convert into third normal form: eliminate all transitive dependencies hence every attribute that is a non-prime should be dependent on the primary key. The following diagram created using Dia software shows the relation in 3NF:
Normalize into Boyce Codd normal form (BCNF): for a relation to be in BCNF, it must be in third normal form and should not have multiple candidate keys which overlap each other. The relations in BCNF are shown below:
Part 2:
The phase 2 IP data model is represented in first normal form as shown below: all repeating groups has been eliminated.
Converting the relations into 2 NF requires that all partial dependencies be removed. As a result, all non-prime attributes will attain fully functional dependencies on the primary keys. The illustration below shows the second normal form for the relations:
In transforming the relations into 3 NF, eliminate transitive dependencies.
Transforming the relations into Boyce Codd normal form (BCNF). This form requires that for a relation to be in BCNF, it must be in third normal form. In fact, it should not contain multiple overlapping candidate keys. The relations in BCNF are illustrated below:
Advanced SQL
Web and Data Warehousing and Mining in the Business World
References
Tripati. (2010, 3 11). www.dotnetfunda.com. Retrieved 2 23, 2016, from www.dotnetfunda.com: http://www.dotnetfunda.com/interviews/show/3477/what-is-the-difference-between-a-database-administrator-and-a-data-adm www.computingstudents.com. (2009, 5). Retrieved 2 23, 2016, from www.computingstudents.com: http://www.computingstudents.com/notes/database_systems/ansi_sparc_3_level_database_architecture.php Zaiane, O. R. (2016, 9 10). www.cs.sfu.ca. Retrieved 2 23, 2016, from www.cs.sfu.ca: http://www.cs.sfu.ca/CourseCentral/354/zaiane/material/notes/Chapter1/node15.html Bagui, S., & Earp, R. (2003). Database design using entity-relationship diagrams. Boca Raton: Auerbach. Kramm, M. A., & Graziano, K. (2000). Oracle designer: A template for developing an enterprise standards document. Upper Saddle River, NJ: Prentice Hall PTR. Kroenke, D. M. (2006). Database processing: Fundamentals, design, and implementation. Upper Saddle River, NJ: Pearson Prentice Hall. Shoval, P. (2007). Functional and object oriented analysis and design: An integrated methodology. Hershey, PA: Idea Group Pub. (An imprint of Idea Group. Tripati. (2010, 3 11). www.dotnetfunda.com. Retrieved 2 23, 2016, from www.dotnetfunda.com: http://www.dotnetfunda.com/interviews/show/3477/what-is-the-difference-between-a-database-administrator-and-a-data-adm www.computingstudents.com. (2009, 5). Retrieved 2 23, 2016, from www.computingstudents.com: http://www.computingstudents.com/notes/database_systems/ansi_sparc_3_level_database_architecture.php Zaiane, O. R. (2016, 9 10). www.cs.sfu.ca. Retrieved 2 23, 2016, from www.cs.sfu.ca: http://www.cs.sfu.ca/CourseCentral/354/zaiane/material/notes/Chapter1/node15.html Bagui, S., & Earp, R. (2003). Database design using entity-relationship diagrams. Boca Raton: Auerbach. Kramm, M. A., & Graziano, K. (2000). Oracle designer: A template for developing an enterprise standards document. Upper Saddle River, NJ: Prentice Hall PTR. Kroenke, D. M. (2006). Database processing: Fundamentals, design, and implementation. Upper Saddle River, NJ: Pearson Prentice Hall. Shoval, P. (2007). Functional and object oriented analysis and design: An integrated methodology. Hershey, PA: Idea Group Pub. (An imprint of Idea Group.
