Phase 3 IP has 2 parts:
Part 1:Analyze the following table (see the Word document called "CS352 - IP3") and reorganize the table into Boyce-Codd Normal Form, at each step describing what is needed to move to the next Normal Form and why each step meets the Normal Form requirements.
- Show unnormalized table given and progression through the normal forms up to Boyce Codd in logical data models.
- Include explanation of how each normal form is met as you progress through the process of breaking down this unnormalized table to tables meeting Boyce Codd normal form.
Part 2:In addition, transform your data model (your EERD created in phase 2 IP) into a logical model, to third normal form. Describe why each table is in third Normal Form. In your logical data model identify the primary keys in each table asbolded and underlinedand each foreign key asitalicized and underlined.
Submission for phase 3 IP includes:
- Logical Data Model for the supplied table(Part 1) with a description of how it moved through UNF to 1NF to 2NF to 3NF and Boyce Codd.
- Logical Data Model for Part 2 with a description of how each table is in third normal form.
Add both parts described to the project template section titled "Database Management Systems."
Charity ID Charity Name Charity Location POC Name POC ID Tel Extn. Customer ID Customer Name Date Contribution Started No of Month Date Places Expected Contribution End CS352 -2101A Advanced Database Systems Unit 1 - Individual Project Demetricus Dixon 1/12/21 Contents The Database Models, Languages, and Architecture3 Database System Development Life Cycle5 Enhanced ER Diagram5 Enhanced ERD Tools5 Multiplicity of relationships6 Database Management Systems7 Advanced SQL8 Web and Data Warehousing and Mining in the Business World9 The Database Models, Languages, and Architecture This architecture describes the structure of a particular database system. It separates the physical database from the user applications. The database is broken down into three levels as external, conceptual, and internal schema. Request and response between these levels is transformed trough mapping. In mapping external and conceptual levels, request and response is transformed to conceptual level and in conceptual land internal mapping, the request is transformed to internal level. The levels can be better understood as · External Schema-This is the view schema of the database. It contains several schemas called subschema. Each subschema describes a portion of database in which a particular group of users is interested and the remaining database is hidden from them. End user interacts with the database at this level. · Conceptual Schema- This schema describes the database design at conceptual or logical level. It describes the structure of the entire database. It describes what data is stored in the database and the what relationships exist among the stored data. Database administrators and programmers work at this level where details of implementation of data structure are hidden. · Internal Schema- This schema describes the physical storage structure of the database. It uses physical data model which defines the data to be stored into low-level data structures or blocks. Data Independence In ANSI-SPARC architecture, the data remains separated from the programs that use it. It remains independent of the changes made to database schema at different levels of the architecture. It allows the data to be used for presentation as well as computing. The data is independent between conceptual and internal level through physical data independence. This independence provides logical description of the database without specifying physical structures. The architecture offers another data independence called logical data independence in which the conceptual schema can be changed without changing any external program and views. Mapping of external and conceptual levels manages any change made to the schema. Role of DA and DBA It is recommended to appoint DA as well DBA since DBA is a technical person who would be involved in technical duties and cannot communicate effectively with business users to build consensus. DBA shall be responsible to control, supervise, execute plans to reach goals, enforce policies, procedures and programming standards, focus on short term goals and deal with technical issues. However, DA shall be responsible to design and implement strategic planning and set long-term goals. DA will be responsible for understanding the business language and translating it into logical data model. Da is more responsible for gathering requirements, analysis and designing phases of database administration. Owing to the current scenario of the company with multiple databases, DBA shall consolidate them into single database without communicating with business users. DA and DBA shall need to work in close coordination as it is imperative that certain skills will cross-pollinate. The DA will better understand the business issues of data and DBA will better understand the physical database. Database System Development Life Cycle Enhanced ER Diagram Enhanced ERD Tools Enhanced ERD tools are required as they offer supertypes, subtypes and inheritance capabilities through graphical representation of entities and relationships within a business structure. Generalization, specialization and constraints are clearly depicted among entities through EERD. The tools facilitate modeling of business design so that it meets the requirements of business. The design should be stable with changing business needs. All the data that needs to be stored should be organized within the model. The tools offer flexibility of re-organizing the stored data to support new information requirements. Appropriate symbols in the model provide simple solution and optimum efficiency to the model. The tools make the business model accessible to users as well as creators and they even offer conformity to integrate the model with existing database structure if required. Multiplicity of relationships One-to-One: · One employee faces one customer at a time. · One customer is attended by one employee. This exists between entities employee and customer who purchases product/service. One-to-Many: · Many employees work in Internal support · A customer can be associated with one or more addresses as a buying customer or selling customer. · Customer may sell many products. Many-to-Many: Many employees may sell same product to customers. Many products may be sold by same employee. This relationship exists between employee who is serving a customer for a product or service. The tables follow constraints of primary key and foreign key. Customer has a unique primary key C_ID. Every customer is listed in this table. A customer can be a supplier. The table Supplier has a foreign key constraint on table Customer. The table Employee has unique primary key for each employee. An employee can be in internal support or managing customers. Therefore, the table Internal_Support has foreign key constraint on table Employee for all employees who work in internal support and table Customer_Employee has foreign key constraint on table Customer_Employee for employees who face customers. A customer who purchases a product or service can do so through an employee. Therefore, the table Customer_Employee has foreign key constraint on table Customer for those customers who wish to purchase. The table Product_Service has unique primary key for each product/service that the company sells. The table Customer_Employee has foreign key constraint on table Product_Service for the product/service that the customer wishes to buy through an employee. Database Management Systems Advanced SQL Web and Data Warehousing and Mining in the Business World