please a,b 1.23 in detail Simple Technology, a Kansas based company, is working

Question

please a,b 1.23 in detail

Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information, and information about projects. Some of the attributes that they want to store in the database is given as a tabular format belo 1. PCode PTitle PManager PBudget PDept PDuration ENum EName ECity EZip EDeptNo EDeptName HRate Payroll Martin 100K Accountin P1 P1 Payrol Martin 100K Accounting 1 P1 Payroll Matin 100KAccounting 12 P2 Budget P2 Budget P2 Budget E13 A Smith Liberty 64068 E30 L Jones Liberty 64068D3 D4 R&D IT R&D R&D R&D 18.5 21 21 E31 P Jouet Liberty 64069 D4 Baker 780K Fnance Baker 780K Finance Baker780K Finance Lewis Lewis E11 | R Ali Liberty/64069/ D4 E13 A Smith Liberty 64068 D4 E9 S Gilbert Buckner 64076D5 Management2 E9 S Gilbert Buckner 64076 D5 E31 P Jouet Liberty 64069 D4 E10 K Ross Buckner64076 D8Sales P3 P3 P4 MillenniumLewi P5 Millennium Martin 200K Hirin Hiring 100K 100K 780K Finance HR HR 24 24 12 anagement 23.25 17 17 R&D 12 E27 A Smith Buckner 64076 D9 Operation HR a) Write all functional dependencies for the above relation. (40 points) b) Using step-by-step process, normalize the above relation to BCNF. Make sure you go through all normalization steps from INF to BCNF. Show your work. Your work must include the following: Indicate all anomalies lead to the violation of each normalization conditions List schemas for the final set of relations that are normalized to BCNF. Make sure to indicate all the primary, foreign, and surrogate keys (if used) in the final schemas. i. ii. ii. Indicate referential integrity constraint for the final set of schemas nd nroiect to design and

Explanation / Answer

USE [master]
GO

/****** Object: Database [Student] Script Date: 09/28/2016 22:51:15 ******/
CREATE DATABASE [Student] ON PRIMARY
( NAME = N'Student', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAStudent.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Student_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAStudent_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Student] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Student].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [Student] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [Student] SET ANSI_NULLS OFF
GO

ALTER DATABASE [Student] SET ANSI_PADDING OFF
GO

ALTER DATABASE [Student] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [Student] SET ARITHABORT OFF
GO

ALTER DATABASE [Student] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [Student] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [Student] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [Student] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [Student] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [Student] SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [Student] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [Student] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [Student] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [Student] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [Student] SET DISABLE_BROKER
GO

ALTER DATABASE [Student] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [Student] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [Student] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [Student] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [Student] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [Student] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [Student] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [Student] SET READ_WRITE
GO

ALTER DATABASE [Student] SET RECOVERY FULL
GO

ALTER DATABASE [Student] SET MULTI_USER
GO

ALTER DATABASE [Student] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [Student] SET DB_CHAINING OFF
GO

USE [Student]
GO

/****** Object: Table [dbo].[stu] Script Date: 09/28/2016 22:52:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[stu](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [LastName] [varchar](50) NULL,
   [FirstName] [varchar](50) NULL,
   [CourseID] [bigint] NULL,
CONSTRAINT [PK_stu] PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[stu] WITH CHECK ADD FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([ID])
GO

USE [Student]
GO

/****** Object: Table [dbo].[Course] Script Date: 09/28/2016 22:51:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Course](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [SectionID] [bigint] NULL,
   [CourseName] [varchar](50) NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Course] WITH CHECK ADD FOREIGN KEY([SectionID])
REFERENCES [dbo].[Section] ([ID])
GO

USE [Student]
GO

/****** Object: Table [dbo].[Section] Script Date: 09/28/2016 22:52:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Section](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [SectionName] [varchar](50) NULL,
CONSTRAINT [PK_Section] PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.