Running Head: ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL Advanced Database Systems (CS352-1804B-01) Project Document Shell Antonio Larkin 12/2/18 1 ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 2 Table of Contents Project Outline ................................................................................................................................ 3 The Database Models, Languages, and Architecture...................................................................... 4 3-Level ANSI-SPARC Architecture ........................................................................................... 4 Data Independence ...................................................................................................................... 6 DA and a DBA ............................................................................................................................ 6 Pros of Having a Separate DA and DBA .................................................................................... 7 Cons of Having a Separate DA and DBA ................................................................................... 7 Database System Development Life Cycle..................................................................................... 8 Database Management Systems ...................................................................................................... 9 Advanced SQL .............................................................................................................................. 10 Web and Data Warehousing and Mining in the Business World ................................................. 11 Logical Data Model for the supplied table and description of how each table……………………………12 References……………………………………………………………………………………16 ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL Project Outline TBD 3 ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 4 The Database Models, Languages, and Architecture It is always a best practice to have a unified database for consistency. It is easy to perform operations on a single database than database scattered in an area. It is also easier to secure a single database than multiple databases. It is also easy to monitor the database and have control over it. 3-Level ANSI-SPARC Architecture The 3 levels of ANSI-SPARC architecture were mainly developed to create independence to various levels of the system and separation of user view. The architecture consists of three levels which are external, conceptual and internal levels. The external level of the database consists of user views which can be modified to suit different users. The level allows the exclusion of data which is not useful to a given user and the user is not allowed to access it. This level provides the independence of user views customizations. Various users can have different customized views accessing the same data without affecting each other. It also helps in hiding the physical details of the database since users do have to deal with the physical storage. The conceptual level defines the type of data been stored in the database as a whole and its relationship. However, this level does not show the way data stored physically. Since this is only one conceptual schema in every database, data integrity is effected at this level (Šikšnys & Pedersen, 2016). The lowest level of the architecture is the internal level and it contains the actual data records, indexes, data fields, and their representation. Each database has only one internal schema. ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 5 The separation of these levels is important since it allows database administrators to change database structure without affecting views accessed by users. In addition, the internal structure is not affected by changes committed to the physical storages aspects. The file below represent the three levels of ANSI-SPARC architecture. Figure 1: Author's diagram for ANSI-SPARC architecture ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 6 Data Independence Data independence is part of data transparency and it has to be considered in DBMS since the user applications store data there. Data independence refers to how well user application will work after changes have been committed in the organization and definition of data. Logical data independence refers to how conceptual schema can be modified without even modifying user applications or the definition of external schema. The physical data independence refers to the ability to change internal schema while not making changes to the external schema. DA and a DBA Data administrator is the person who is responsible for controlling data of a particular database in an organization. Database administrators are responsible for the design and controlling the use of a database in an organization. Although both are responsible for managing database they differ when it comes to their responsibilities and required skills. DA is responsible for determining the data to be stored in a database depending on the organization's database. It is not a must for him/her to be a technical person but any know; edge about database technology is an added advantage. DA is most focused on the business use of data and for this reason, he/she is required to contribute requirement gathering and analysis as well as the design phase of the database (Coronel & Morris, 2016). DBA is responsible for creating a database which is fully functional and provides any necessary support during database implementation process.in addition, he/she should have enough knowledge of database technology but it is not a must a DBA to business oriented person. DBA is responsible for designing the database, developing and testing it and make sure it is fully operational. ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL Pros of Having a Separate DA and DBA Separating DA and DBA improves the accuracy of the data store and functionality of the database. Both have enough time to perform their tasks. The diversity of ideas is supported by having separate DA and DBA since they will brainstorm together and come up with a reliable database and data. In addition is easy for the two to perform divide and conquer to solve a large problem easily. Cons of Having a Separate DA and DBA Having separate DA and DBA is not cost effective since the organization has to pay two people instead of ones. The DA may lack the knowledge of database technology creating a communication and understanding problem between him/her and database administrator. 7 ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 8 Database System Development Life Cycle Entity relationship date model has been in use for a period of 35 years. This data modeling usually uses databases due to its fairly abstract and also because it’s easy to discuss as well as explain. It models which are also known as ER schema are mainly represented by the ERD models. The model is based on two concepts; entities and relationship. Entities are tables holding specific information and the relationship is the link between the entities. ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 9 Database Management Systems DBMS is system software used in creating as well as managing databases. The system software gives its user as well as programmers a systemic way in creating, retrieving, and updating as well as managing data. It enables end user to read, create, delete as well as update database. The system significantly serves as an interface between the database as well as end user ensuring consistency of data in a well-organized manner and easily accessible. DBMS usually manages three significant things; database engine, data, and data schema. The foundational elements usually help in providing security, data, concurrency, uniform in the administration procedure and integrity. DBMS offer physical and logical data independence. This means that DBMS can protect applications and users from the urge of wanting to know the area where data is stored or urge of wanting to know the changes to the physical structure of data which is in both hardware and storage. ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 10 Advanced SQL The word SQL means Structured Query language. This type of query language communicates with database. SQL is as a standard language data on a database. Advanced SQL enables one to perform several dealings on the underlying database data. Also, it enables that user in retrieving simple to more complex requests in a better way. ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 11 Web and Data Warehousing and Mining in the Business World Both data warehousing and data mining are key pillars in providing information for business intelligence. Web and data warehousing as well as mining play key roles in the world of business. Data mining for example refers to the computer assisted process that entails digging through a well as analyzing enormous varieties of data and later extracting the meaning of data. Data mining tools are responsible for predicting behaviors and future trends that gives businesses a chance to make knowledgeable decisions. Again, data mining identifies the common characteristics of consumer who purchase common products from the company through a process known as market segmentation. Additionally, it reveals the differences that exist between a typical client in the current and previous month. Lastly, data mining generates new business opportunities through automated prediction of trends and behaviors. A good example of a predictive challenge is targeted marketing, forecasting bankruptcy as well as identifying segments of a population. ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 12 Normalization in DBMS Un-Normalized Form (UNF) If the attributes of a given table aren’t related, the table is considered to be an un-normalized table. The said attributes are mostly composed of several values in rows. Based on the fact that the attributes in the table aren’t a subset of one another, the dependencies can be classified as non-trivial and include: POC ID ----> Tel Ext, POC ID, POC Name. Customer ------> Customer Name, Customer ID Charity ------> Charity Location, Charity Name, Charity ID First Normal Form The First Normal Form in a given table is mostly as a result of no duplication of rows within the said table. This means that the cells contain figures that aren’t in repetition. ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 13 Database (Charity Name Charity Location Charity ID, POC ID, POC Name Tel Ext., Customer Name, Customer ID) The making of the columns one of a kind can be done via the summation of keys which are primary to POC ID and Charity ID Second Normal Form A given table can only exist in the Second Normal Form if the table first exists in the First Normal Form and if all the characteristics that are key are relied on by the characteristics that aren’t key. This will result in the modification of the column dependencies. POC ID ------> POC ID, Tel Ext., POC Name. Customer -------> Customer ID, Customer Name Charity -------> Charity ID, Charity Name, Charity Location Third Normal Form A table exists in the Third Normal Form if no there are no functional dependencies which hold the virtue of transitivity and the table exists also in the Second Normal Form. Charity ------> Charity Location, Charity ID, Charity Name POC ID ------> Tel Ext., POC Name, POC ID ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 14 Customer -----> Customer Name, Customer ID Boyce Codd Normal Form Boyce Codd Normal Form or BCNF in short refers to a table in which the columns can uniquely identify any database and the table is present in the Third Normal Form. In checking the factors that affect the outcome, he connection must contain ALL characteristics from the left and a MINIMUM of one from the right. Transaction -----> POC ID, Customer ID Charity ID Transaction ID Customer -----> Customer Name Customer ID, POC ID -----> Tel Ext. POC ID, POC Name, Charity -----> Charity Name, Charity ID, Charity Location ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL 15 Logical Diagram This gives a view of conceptual information shaping, development and execution. It is brought into existence ahead of the physical model and differs from the information model. It depicts an organizations business prerequisites. Other than its depiction of business rules, it contains the business information model demonstrated by essential groups, characteristics and all distinct objects. ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL References Solution, I., HW- variables, i., & Solution, H. (2018). CS352 - IP3 Solution. Retrieved from https://sellfy.com/p/i53k/ Teorey, T., Lightstone, S., & Nadeau, T. (2005). Database modeling & design. Amsterdam: Elsevier. Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management. Cengage Learning. Šikšnys, L., & Pedersen, T. B. (2016, July). Solved: Integrating optimization problem solvers into SQL databases. In Proceedings of the 28th International Conference on Scientific and Statistical Database Management (p. 14). ACM. 16
Purchase answer to see full attachment