UOPX Database Normalization: Physical database design Implement the normalized database design from the Week Three individual assignment into a physical da
UOPX Database Normalization: Physical database design Implement the normalized database design from the Week Three individual assignment into a physical database design.
Complete the following:
Create a new database.
Develop the tables, with attributes and appropriate data types, using Microsoft® SQL Server® Management Studio.
Assign primary and foreign keys to the tables.
Capture and paste screenshots that document your physical database design including the process you went through in implementing the physical design, into a PowerPoint® presentation.
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. Running Head: Database Normalization
1
Database Normalization
David Heininger
University of Phoenix
Database Normalization
2
USE [master]
GO
/****** Object:
******/
Database [BookStore]
CREATE DATABASE [BookStore] ON
Script Date: 2/3/2019 2:15:10 AM
PRIMARY
( NAME = N’BookStore’, FILENAME = N’C:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLDATABookStore.mdf’ , SIZE = 2048KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BookStore_log’, FILENAME = N’C:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLDATABookStore_log.ldf’ , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [BookStore] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [BookStore].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [BookStore] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [BookStore] SET ANSI_NULLS OFF
GO
ALTER DATABASE [BookStore] SET ANSI_PADDING OFF
Database Normalization
3
GO
ALTER DATABASE [BookStore] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [BookStore] SET ARITHABORT OFF
GO
ALTER DATABASE [BookStore] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [BookStore] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [BookStore] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [BookStore] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [BookStore] SET CURSOR_DEFAULT
GLOBAL
GO
ALTER DATABASE [BookStore] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [BookStore] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [BookStore] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [BookStore] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [BookStore] SET
DISABLE_BROKER
GO
ALTER DATABASE [BookStore] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
Database Normalization
4
GO
ALTER DATABASE [BookStore] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [BookStore] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [BookStore] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [BookStore] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [BookStore] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [BookStore] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [BookStore] SET RECOVERY SIMPLE
GO
ALTER DATABASE [BookStore] SET
MULTI_USER
GO
ALTER DATABASE [BookStore] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [BookStore] SET DB_CHAINING OFF
GO
USE [BookStore]
GO
/****** Object:
******/
SET ANSI_NULLS ON
Table [dbo].[Author]
Script Date: 2/3/2019 2:15:10 AM
Database Normalization
5
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Author](
[Authorid] [nchar](10) NULL,
[FirstName] [nchar](10) NULL,
[LastName] [nchar](10) NULL
) ON [PRIMARY]
GO
/****** Object:
Table [dbo].[Book]
Script Date: 2/3/2019 2:15:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book](
[title] [nchar](100) NULL,
[AuthorID] [nchar](100) NULL,
[Genre] [nchar](100) NULL,
[publicationDate] [smalldatetime] NULL,
[ISbn] [nchar](100) NULL
) ON [PRIMARY]
GO
/****** Object:
******/
SET ANSI_NULLS ON
GO
Table [dbo].[Custumer]
Script Date: 2/3/2019 2:15:10 AM
Database Normalization
6
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Custumer](
[Custid] [nchar](10) NULL,
[fname] [nchar](10) NULL,
[lname] [nchar](10) NULL,
[phone] [nchar](10) NULL,
[address] [nchar](10) NULL
) ON [PRIMARY]
GO
/****** Object:
******/
Table [dbo].[Publisher]
Script Date: 2/3/2019 2:15:10 AM
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Publisher](
[publisherid] [nchar](10) NULL,
[pubName] [nchar](10) NULL,
[PubCountry] [nchar](10) NULL
) ON [PRIMARY]
GO
/****** Object:
Table [dbo].[Sale]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
Script Date: 2/3/2019 2:15:10 AM ******/
Database Normalization
7
GO
CREATE TABLE [dbo].[Sale](
[Saleid] [nchar](10) NULL,
[custid] [nchar](10) NULL,
[isbn] [nchar](10) NULL
) ON [PRIMARY]
GO
USE [master]
GO
ALTER DATABASE [BookStore] SET
GO
READ_WRITE
Database Normalization
Using MS Studio Tools
David Heininger
University of Phoenix
DAT/105
Creating a new database
Making Tables: Author Table
Making Tables: Books
Making Tables: Publisher
Making Tables: Customer
Making Tables:Sale
Specifying Indexes: Sales
Creating Views
Relationships
Establishing Foreign Keys
Setting Up relationships
Setting up relationship between book and
Author
Final ERD
Purchase answer to see full
attachment
