Based on the feedback you have received with respect to your data model and DDL, now is your chance to implement the final changes. Make any needed changes to your ERD and Physical Data Model and submit for final review. Include any SQL needed for the database, the DDL, the DML to manage the customer and employee rows, and the 3 SELECT statements.In addition, submit the star schema and the DDL to create the Star schema for the data warehouse. You want a single Fact table to track all orders with the following dimensions: TimeCustomerProductEmployee Be sure to include all DDL including primary and foreign keys; feel free to create new or needed primary keys. Finally, a specific and detailed discussion about the ETL process is to be used to move data from the OLTP environment to the data warehouse.Your submission should include the following:A description on your approachThe features of your enhanced ERDA discussion about handling the M:M relationship between customer and productsThe approach used to ensure 3NFRequired SQL statements for the databaseStar schema for the fact table and its 4 dimension tablesThe DDL for the Star schema for the data warehouseA description of the ETL
cs352_antoniolarkin_ip4.docx

Unformatted Attachment Preview

Running Head: ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL
Advanced Database Systems (CS352-1804B-01)
Project Document Shell
Antonio Larkin
12/7/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
SQL for the DDL, DML Create the tables Advanced SQL
Create the primary keys
Create the foreign keys………………………………………………………………………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
16
Advanced SQL
Using the DBMS you chose in the previous Discussion Board assignment, download and install that software
to prepare for the Database and Data Model to be created. Once the software is running and the database is
available, complete the following:
Create the physical data model for the logical data model that you submitted in IP3. This should include all
the data definition language SQL.
Your submission should include all DDL needed to:



Create the tables
Create the primary keys
Create the foreign keys
▪ Charity Table
CREATE TABLE Charity (
Charity_ID LONG NOT NULL,
Charity_Name TEXT (25) NOT NULL,
Charity_Location TEXT (25) NOT NULL,
PRIMARY KEY (Charity_ID)
);

Customers Table
CREATE TABLE Customers (
Customer_ID LONG NOT NULL,
Customer_Name TEXT (25) NOT NULL,
PRIMARY KEY (Customer_ID)
);
ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL

POC Table
CREATE TABLE POC (
POC_ID LONG NOT NULL,
POC_Name TEXT (25) NOT NULL,
PRIMARY KEY (POC_ID)
);

Transaction Table
CREATE TABLE Transaction (
Trans_ID LONG NOT NULL,
Charity_ID LONG NOT NULL,
POC_ID LONG NOT NULL,
Customer_ID LONG NOT NULL,
PRIMARY KEY (Trans_ID),
FOREIGN KEY (Charity_ID) REFERENCES Charity(Charity_ID),
FOREIGN KEY (POC_ID) REFERENCES POC(POC_ID),
FOREIGN KEY (Customer_ID) REFERENCES
Customers(Customer_ID)
);

Add DML statements to:
o Add data of 1 customer who buys from the company
INSERT INTO Customers (Customer_ID, Customer_Name) VALUES (‘1 ‘, ‘Sherlock’);
o
Provide the DML to add 1 employee who interacts with customers
INSERT INTO POC (POC_ID, POC_Name) VALUES (‘1 ‘, ‘Watson’);
17
ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL
o
18
Give DML to change data of the employee, giving the commission a 25% increase
SELECT POC.[POC_ID], POC.[POC_Name], Transaction.Trans_ID
FROM POC INNER JOIN [Transaction] ON POC.POC_ID = Transaction.POC_ID
WHERE (((Transaction.Trans_ID)=”125%”));
o
Give DML to delete the customer and employee data
DELETE FROM Customers WHERE Customer_Name=’Sherlock’;
DELETE FROM POC WHERE POC_Name=’Watson’;

Write 3 SELECT statements:
o To select the customer details
SELECT Customers.[Customer_ID], Customers.[Customer_Name] FROM Customers;
o
To select the employees details
SELECT POC.[POC_ID], POC.[POC_Name] FROM POC;
o
To show which employee services which customer
SELECT Transaction.[POC_ID], Transaction.[Customer_ID] FROM [Transaction];
Add the SQL for the DDL, DML, and SELECT statements to the project template section titled “Advanced SQL.”
Name the document CS352__IP4.doc.
Submit your Word document and make sure that it contains the following:

Screenshot of the ERD, logical data model from previous assignments.
ADVANCED DATABASE SYSTEMS PROJECT DOCUMENT SHELL



The DDL to create the tables, including the table definition and the primary and foreign key
definitions.
SQL to add data to the tables
o Add data of 1 customer who buys from the company
o Provide the DML to add 1 employee who interacts with customers
o Give DML to change data of the employee, giving the commission a 25% increase
o Give DML to delete the customer and employee data
Write 3 SELECT statements:
o To select the customer details
o To select the employees details
o To show which employee services which customer
19
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.
20

Purchase answer to see full
attachment