Design and Create a database for a dog show using MYSQL or SQL Server. Each dog
ID: 3721332 • Letter: D
Question
Design and Create a database for a dog show using MYSQL or SQL Server.
Each dog has a breed, a category, and a handler and some descriptive data.
Handler's have names, gender, addresses, etc. A handler may handle more than one dog.
Breeds fall into one of the following categories
Group 1, Sporting Dogs.
Group 2, Hounds.
Group 3, Working Dogs.
Group 4, Terriers.
Group 5, Toys.
Group 6, Non-Sporting.
Group 7, Herding.
Each contest has a win, place, and show (1st, 2nd, 3rd)
Each dog participates in a contest that is restricted to it's own category
Each contest has three judges. The judges each judge 2 contests but no more. A judge cannot be handler.
show your code for creating the database.
Explanation / Answer
--**************Data Base Creation*************
CREATE DATABASE [DOGSHOW]
ON PRIMARY
( NAME = N'DOGSHOW', FILENAME = N'C:MY DB PLACEDOGSHOW.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DOGSHOW_log', FILENAME = N'C:MY DB PLACEDOGSHOW_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE [DOGSHOW]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [DOGSHOW] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
--**************Category Table Creation*********
CREATE TABLE [dbo].[Category]
(
[ID] [int] NOT NULL, [Category] [varchar](20) NOT NULL, [GroupDesc] [varchar](20) NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ([ID] ASC ) ON [PRIMARY]
) ON [PRIMARY]
GO
--**************Handler Table Creation**********
CREATE TABLE [dbo].[Handler]
(
[ID] [int] NOT NULL, [Name] [varchar](50) NOT NULL, [Gander] [bit] NULL, [Address] [varchar](200) NULL,
CONSTRAINT [PK_Handler] PRIMARY KEY CLUSTERED ([ID] ASC ) ON [PRIMARY]
) ON [PRIMARY]
GO
--**************Dog Table Creation**************
CREATE TABLE [dbo].[DOG]
(
[ID] [int] NOT NULL, [Breed] [varchar](50) NOT NULL, [CategoryID] [int] NOT NULL, [HandlerID] [int] NOT NULL, [Descrp] [varchar](200) NULL,
CONSTRAINT [PK_DOG] PRIMARY KEY CLUSTERED ([ID] ASC ) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DOG] WITH CHECK ADD CONSTRAINT [FK_DOG_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([ID])
GO
ALTER TABLE [dbo].[DOG] WITH CHECK ADD CONSTRAINT [FK_DOG_Handler] FOREIGN KEY([HandlerID])
REFERENCES [dbo].[Handler] ([ID])
GO
--**************Judge Table Creation************
CREATE TABLE [dbo].[Judge]
(
[ID] [int] NOT NULL, [Name] [varchar](20) NOT NULL,
CONSTRAINT [PK_Judge] PRIMARY KEY CLUSTERED ([ID] ASC ) ON [PRIMARY]
) ON [PRIMARY]
--**************Contest Table Creation**********
CREATE TABLE [dbo].[Contest]
(
[ID] [int] NOT NULL, [CategoryID] [int] NOT NULL, [DogID] [int] NOT NULL, [Win] [varchar](20) NULL, [Place] [varchar](20) NULL,
[Show] [varchar](5) NULL, [Judge1] [int] NOT NULL, [Judge2] [int] NOT NULL, [Judge3] [int] NOT NULL,
CONSTRAINT [PK_Contest] PRIMARY KEY CLUSTERED ([ID] ASC ) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contest] WITH CHECK ADD CONSTRAINT [FK_Contest_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([ID])
GO
ALTER TABLE [dbo].[Contest] WITH CHECK ADD CONSTRAINT [FK_Contest_Dog] FOREIGN KEY([DogID])
REFERENCES [dbo].[DOG] ([ID])
GO
ALTER TABLE [dbo].[Contest] WITH CHECK ADD CONSTRAINT [FK_Contest_Judge1] FOREIGN KEY([Judge1])
REFERENCES [dbo].[Judge] ([ID])
GO
ALTER TABLE [dbo].[Contest] WITH CHECK ADD CONSTRAINT [FK_Contest_Judge2] FOREIGN KEY([Judge2])
REFERENCES [dbo].[Judge] ([ID])
GO
ALTER TABLE [dbo].[Contest] WITH CHECK ADD CONSTRAINT [FK_Contest_Judge3] FOREIGN KEY([Judge3])
REFERENCES [dbo].[Judge] ([ID])
GO
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.