ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 1
Advanced Database Systems
Individual Project 2
James Larkin
2-24-18
Table of Contents
Project Outline
Phase 1IP: The Database Models, Languages, and Architecture
Phase 2IP: Database Systems Development Life Cycle
Phase 3IP: Database Management System Logical Data Model
Phase 4IP: Advanced SQL
Phase 5IP: Web and Data Warehousing and Mining in the Business World
References
Project Outline
This project will assist in the design and implementation of several databases for a company that needs a central point of reference. The company’s current databases keep track of customer’s information as well as suppliers. In addition, the database keeps track of the sales and product information. In this outline, more information regarding the database development will be added on a weekly basis
Phase 1IP: The Database Models, Languages, and Architecture
The company can benefit from spending time planning the DMMS process to meet the perceived needs by following the 3-level (ANSI-SPARC) The American National Standards Institute, Standards Planning, and Requirements Committee. This is the architecture used in designing database management systems. The ANSI-SPARC comprises a three-level architecture that comes loaded with separate schemas. The benefit of planning the DBMS design that it helps in identifying all the DBMS requirement, thus making the whole implementation process less troubling.
The 3-level architecture for DBMS provided by the 3-level (ANSI-SPARC) is the physical level, the conceptual level, and the external level. These 3 DBMS levels are connected through a mapping process that transforms requests and responses between the database levels. However, small databases do not need mapping because the process takes a longer duration to complete (Connolly & Begg, 2015). The internal or conceptual mapping concept of the DBMS transforms request located on the external schema against the conceptual schema. On the other hand, the conceptual/ internal mapping transforms all the requests from conceptual to internal levels.
The physical level of DBMS describes the physical structures of the database that is used for storage. It is also referred to as the internal level and is stored in form of bits at the lowest level of the secondary database. However, it is viewed in form of files at the highest level of the database. The various data types stored in the physical level architecture are defined by the internal schema that uses physical data model (Connolly & Begg, 2015). The conceptual level is the level that gives a description of the entire database. It is the data model and represents the content of the database. It contains the information required to build relevant external records and hides the internal details that are applicable to physical details.
The external level of DBMS architecture has related the data as viewed by the external user. All the different views of external users to the external schema are incorporated in the external level architecture. It is close to the database user and provides a description of the segment of the database that’s is needed by a particular user group. And ensures that all other users are hidden.
Data independence is a type of transparency in data which is of significance in a centralized DBMS. Data independence is the immunity attributed to a user application to all the altercations in the data organization and definition. It may be in the form of physical data independence where the DBMS hides all the information in respect to the storage structure form the user applications (Connolly & Begg, 2015). In this case, the application is not supposed to get involved in any issue to do with the data storage give that no difference exists between the operations on the data. Physical independence provides immunity of logical models to any alteration that takes place in the internal model and takes place at the logical interface level.
In other instances, it may be in the form of local data independence where the information is stored based on how it is managed within the database. It includes the relation and constraints applicable to a particular database. Through logical independence, data can be liberalized form the actual data stored in the database. Logical data independence provides immunity of external models to any alteration that occurs in the logical model and takes place at the user interface level.
The role of a DA is to undertake overall management of the data, data architect and information as well as other data resources in the database of an organization. The DA needs programming a system analysis skills to implement data policies, standards, and procedures.in addition, the DA is responsible for resolving data ownership conflicts, undertaking data analysis to define the operational requires, business rule, data requirements and the database design (Connolly & Begg, 2015). The DA also undertakes the planning of the data administration to develop the enterprise model, IT strategy, cost-benefit model and maintaining corporate data dictionary. The DA also manages the data repository and undertakes internal marketing of all the DA concepts.
On the other hand, the DBA is a technical and operational expertise that undertakes the physical design process of the database. The DBA also implement database security policies as ensure sits performance as required. Thus, the DBA is responsible for maintaining the data dictionary, enforcing the database security and stands as per the organization's policy. The DBA also monitors the performance of the database at all times. Since the roles and job descriptions of a DA and a DBA differ, I would recommend that the company should hire two people to perform the task
The pros of having a separate DA and DBA is that it facilitates several growths and to accommodate multiple businesses under the same owner. It also creates consistency when data is backed up while at the same time allowing high availability of dirt cheap. DBA and DA allows the security of the database and inputs policies and procedures that ensure there is sufficient control. The cons are that it lacks legal protection for the owner of the business (Connolly & Begg, 2015). It is only a functional name that gives the company a brand. The separated applications will increase coupling thus leading to development restrictions. It also leads to consolidation of log files into a single log. It is therefore recommended that an entity poses both a DA and a DBA.
In this project, here is the database of the company that has been outlined in this paper. The database below will be the Entity Relationship Diagram in which the company must obtained.
Figure 1: ERD of Company
This database needs an enhancement in order to get effective and concise relationship within the participants of company's transaction details about the selling and purchasing of the products from their suppliers. Now, in order to enhance their database, the database shown above should include the contact details of the customer and supplier and even the contact details of their employees. This is to ensure the loyalty of their customer and in order to increase their sales by keeping their contact information for some updates for future transactions. Also, it should have order line, inventory, supplier information, and the involvement of the organizations to their employee for essential for implementing both internal and external roles in regards with the salaries, training, and commission rate of the employees. As we enhance this database, everything will be organized for effective monitoring of transaction process, from ordering the products from company's suppliers, selling it to the company's customers, and then, back to purchasing of products again to the suppliers. This is just a kind of cycle that will be happened either weekly, or monthly or every day.
Phase 2IP: Database Systems Development Life Cycle
Figure 2: Enhance ERD Of Company
As mentioned the above recommendation of enhancing the database of the company, here is the result of enhance database that shown above. Now, we got the order of the enhance database. Let's have the OrderLine table. This table will lists the order of the customer and the quantity of the ordered products. Next table that we have put to enhance the database is the table of Inventory. In this table, we put the lists of the name of product, stock quantity and together with its process. With this, we can clearly monitor how many products that have been purchase and sold. In any database or in any kind of transaction process, it is very important to have an inventory in order to have a proper tally of products left and products that have been dismissed/sold. next table is the supplier table.
This table will lists the supplier and customer ID, date sales, total sale, and the resale of the items. In this table, we indicate both customer and the supplier, as they were both the main subject in this transaction. Next, is the contact table. In this table, we lists the contact information of customer, supplier, and the employee. Their contact information that must be collected are as follows: phone, street, city, and their zip code. Notice that the organization is responsible to support the employee's salary, commission rate, and for their training. The product that being purchase will be handled in the order line, and then, it will be done in the process of inventory together with those products that being ordered from the suppliers.
Phase 3IP: Database Management System Logical Data Model
Phase 4IP: Advanced SQL
Phase 5IP: Web and Data Warehousing and Mining in the Business World
References
Anne Marie Smith, April 1, 2002, Data Warehousing & ERP - A Combination of Forces, Published in TDAN.com April 2002
Connolly T. & Begg C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson Education.
Lafler, Kirk Paul (2011), “Powerful and Sometimes Hard-to-find PROC SQL Features,” PharmaSUG 2011 Conference, Software Intelligence Corporation, Spring Valley, CA, USA.
Lafler, Kirk Paul (2010), “Exploring Powerful Features in PROC SQL,” SAS Global Forum (SGF) Conference, Software Intelligence Corporation, Spring Valley, CA, USA.
Lafler, Kirk Paul (2009), “Exploring DICTIONARY Tables and SASHELP Views,” South Central SAS Users Group (SCSUG) Conference (November 8th – November 10th, 2009), Software Intelligence Corporation, Spring Valley, CA, U