DAT 105 Phoenix Database Normalization Assignment Normalize the tables from the logical database design from the Week Two individual assignment and generat

DAT 105 Phoenix Database Normalization Assignment Normalize the tables from the logical database design from the Week Two individual assignment and generate a new normalized ERD.

Complete the following:

Use the Microsoft® SQL Server® 2014 Management Studio Database Diagramming tools to normalize the existing tables in the Week Two logical database design.
Indicate primary keys, foreign keys, new entities, and/or new attributes in the normalized ERD.

Capture and paste screenshots of the normalization process you went through in creating the design, into a PowerPoint® presentation or Word document for submission.

Note: The Learning Team Collaborative Discussion from this week will assist you with key areas of this assignment.

Submit your presentation to the Assignment Files tab above. SQL Database for a Book Store
David Heininger
University of Phoenix
Kurt Jost
DAT/105 – Introduction to Database
Concepts/Roles
January 28, 2019
INTRODUCTION
• The database contains information about the books held in stock, their
authors, publishers, customers, sales.
• The database was populated with information from bibliographic records
in IUC at (with some modifications) and do not necessarily represent actual
publications.
• The SQL queries are meant to gather different kinds of useful
information about the business.
The process of creating the
logical design of the database
• The process of creating the logical design involved, arranging data
into a series of logical relationships known as entities & attributes.
• An entity being a chunk of information.
• In the relational database, the entity was mapped to a table.
• An attribute is a component of an entity that helped to define the
uniqueness of the entity.
• In the relational database, the attribute was mapped to a column.
Data Dictionary
Entity Relationship Diagram
The process for selecting the appropriate
primary keys and data types
• A primary key is simply a column that uniquely identifies each row in the table.
• The appropriate primary keys were created while creating the database table using the
CREATE TABLE statement.
• A primary key constraint for the database tables was created by specifying the primary key
in the primary key column’s definition.
• A database table comprised of several columns with specific data types such as numeric or
string.
• An appropriate data type for each column was selected for the data stored in that column
Query 1
Lists all titles in “book” and
includes ISBN, author name (as
combined from author.fname and
author.lname) and the publisher
name.
SELECT
book.title AS “Title”, book.isbn AS
“ISBN”, CONCAT(author.fname, ‘ ‘,
author.lname) AS “Author”,
publisher.name AS “Publisher”
FROM book, author, publisher
WHERE
author.author_id=book.author_id
AND
publisher.publisher_id=book.publis
her_id;
Query 2
Lists all customers who have
purchased books published since
2007.
SELECT customer.fname,
customer.lname, book.title,
book.publication_date
FROM customer
JOIN sale ON
customer.customer_id=sale.cust_i
d
JOIN book ON sale.isbn=book.isbn
JOIN author ON
author.author_id=book.author_id
WHERE book.publication_date >=
2007;
Query 3
Lists customers (as combined from
customer.fname and customer.lname) who
have purchased books published in the UK
or the US, as well as the title of the book
they purchased and the name of its
publisher and order by last name of
customer.
SELECT CONCAT(customer.fname, ‘ ‘,
customer.lname), book.title,
publisher.country, publisher.name
FROM customer
JOIN sale ON customer.customer_id =
sale.cust_id
JOIN book ON sale.isbn = book.isbn
JOIN publisher ON book.publisher_id =
publisher.publisher_id
WHERE publisher.country =’UK’ or
publisher.country =’US’
ORDER BY customer.lname;
Query 4
Lists the number of books sold
that have been written by each
author and group by author’s first
name, then last name.
SELECT COUNT(sale.isbn) AS
“Number of books sold”,
CONCAT(author.fname, ‘ ‘,
author.lname) AS “Author”
FROM sale
JOIN book ON sale.isbn =
book.isbn
RIGHT JOIN author ON
book.author_id=author.author_id
GROUP BY author.fname,
author.lname;
Query 5
Lists the different (distinct) genres and how
many books belong to each genre, order
alphabetically by genre.
SELECT distinct book.genre,
COUNT(book.genre)
FROM book
GROUP BY book.genre
ORDER BY book.genre ASC;
Query 6
Lists the names and phone numbers of all distinct
customers who have ordered books published by US or
UK publishers, sort them alphabetically.
SELECT DISTINCT (CONCAT (customer.fname, ‘ ‘,
customer.lname)) AS “Customer Name”,
customer.phone AS “Customer Phone Number”
FROM customer
JOIN sale ON sale.cust_id = customer.customer_id
JOIN book ON book.isbn = sale.isbn
WHERE (CONCAT (customer.fname, ‘ ‘,
customer.lname)) IN (SELECT CONCAT(customer.fname,
‘ ‘, customer.lname)
FROM customer
JOIN sale ON customer.customer_id = sale.cust_id
JOIN book ON sale.isbn = book.isbn
JOIN publisher ON book.publisher_id =
publisher.publisher_id
WHERE publisher.country =’UK’ or publisher.country
=’US’)
ORDER BY CONCAT(customer.fname, ‘ ‘,
customer.lname);
Query 7
Lists customers’ first and last names ordered by
the number of books they have purchased,
ordered by the number of books purchased.
SELECT COUNT(sale.cust_id) AS “Number
of Books Purchased”,
CONCAT (customer.fname, ‘ ‘, customer.lname)
AS “Customer Name”
FROM sale
JOIN customer ON customer.customer_id =
sale.cust_id
GROUP BY customer.fname, customer.lname
ORDER BY (COUNT(sale.cust_id)) DESC;
Query 8
Lists the number of records in each table (book,
author, sale, customer, and publisher).
SELECT
DISTINCT (SELECT COUNT(*) FROM
book) AS “Books”,
(SELECT COUNT(*) FROM author) AS
“Authors”,
(SELECT COUNT(*) FROM sale) AS “Sales”,
(SELECT COUNT(*) FROM customer) AS
“Customers”,
(SELECT COUNT(*) FROM publisher) AS
“Publishers”
FROM book
JOIN author ON author.author_id =
book.author_id
JOIN sale ON sale.isbn = book.isbn
JOIN customer ON
customer.customer_id=sale.cust_id
JOIN publisher ON publisher.publisher_id =
book.publisher_id;
Query 9
Creates and display a view of all sales
with customer name, title of book,
author last name, isbn of book, and
publisher name.
CREATE VIEW listsales AS
SELECT CONCAT(customer.fname, ‘
‘, customer.lname) AS “Customer
Name”, book.title AS “Book”,
author.lname AS “Author Last
Name”, book.isbn AS “ISBN”,
publisher.name AS “Publisher”
FROM customer
JOIN sale ON customer.customer_id
= sale.cust_id
JOIN book ON sale.isbn = book.isbn
JOIN author ON author.author_id =
book.author_id
JOIN publisher ON book.publisher_id
= publisher.publisher_id
ORDER BY customer.lname;
SELECT * FROM listsales;
Query 10
Books are organized by ISBN—Books
with ISBNs greater than 700000000 are
housed on the third floor, those with
ISBNs between 3000000000 and
700000000 are housed on the second
floor, and all others are on the first
floor. List all books with their
locations.
SELECT book.title AS “Title”, CASE
WHEN book.isbn >= 700000000
THEN ‘Located on Third Floor’
WHEN book.isbn
Purchase answer to see full
attachment