Implement database, ER model Solution
1. Creates database named HW1-YourInitials
CREATE DATABASE HW1-YourInitials
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = HW1-YourInitials)
DROP database abc
GO
2. Create tables without any unique, primary key, foreign key, or check constraints
a. Only include NULL and Default constraints
{'/*****************Student Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Student] Script Date: 19/02/26 10:35:53 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[G-Number] [char](9) NULL,
[Last Name] [varchar](20) NULL,
[First Name] [varchar](20) NULL,
[Street Num] [varchar](6) NULL,
[Street Name] [varchar](50) NULL,
[City] [varchar](20) NULL,
[State] [char](3) NULL,
[Zip] [char](5) NULL,
[SSN] [char](9) NULL,
[Major code] [varchar](4) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/**********'} {'Registration Table script***************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Registration] Script Date: 19/02/26 10:40:24 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Registration](
[Student G-Number] [char](9) NULL,
[CRN] [char](5) NULL,
[regDateTime] [datetime] NULL,
[Grade] [char](2) NULL,
[Status] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/*****************'} {'Registration Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Registration] Script Date: 19/02/26 10:45:59 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Registration](
[Student G-Number] [char](9) NULL,
[CRN] [char](5) NULL,
[regDateTime] [datetime] NULL,
[Grade] [char](2) NULL,
[Status] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/*****************'} {'Course Offering Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Course Offering] Script Date: 19/02/26 10:51:50 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Course Offering](
[CRN] [char](5) NULL,
[Semester] [char](2) NULL,
[SemYear] [char](4) NULL,
[Section] [varchar](3) NULL,
[Max Students] [int] NULL,
[Faculty G-Number] [char](9) NULL,
[Subject Code] [char](4) NULL,
[Course Number] [char](3) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/*****************'} {'Course Timings Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Course Timings] Script Date: 19/02/26 10:56:46 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Course Timings](
[Schedule Code] [char](10) NULL,
[Day of Week 1] [char](1) NULL,
[Begin Time 1] [datetime] NULL,
[End Time 1] [datetime] NULL,
[Day of Week 2] [char](1) NULL,
[Begin Time 2] [datetime] NULL,
[End Time 2] [datetime] NULL,
[Room Number] [char](4) NULL,
[Building Code] [char](3) NULL,
[CRN] [char](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/*****************'} {'Classrooms Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Classrooms] Script Date: 19/02/26 11:00:23 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Classrooms](
[Room Number] [char](4) NULL,
[Building Code] [char](3) NULL,
[Seating Capacity] [int] NULL,
[Computer] [bit] NULL,
[Projector] [bit] NULL,
[Laptop Hookup] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/*****************'} {'Buildings Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Buildings] Script Date: 19/02/26 11:04:51 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Buildings](
[Building Code] [char](3) NULL,
[Building Name] [varchar](30) NULL,
[Street Number] [varchar](4) NULL,
[Street Name] [varchar](50) NULL,
[Mail Stop Num] [varchar](3) NULL,
[City] [varchar](20) NULL,
[State] [char](2) NULL,
[Zip] [char](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/*****************'} {'Department Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Department] Script Date: 19/02/26 11:06:28 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Department](
[DEPT CODE] [char](4) NULL,
[Dept Name] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/*****************'} {'Faculty Appointment'} {'Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Faculty Appointment] Script Date: 19/02/26 11:09:14 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Faculty Appointment](
[DEPT CODE] [char](4) NULL,
[Faculty G-Number] [char](9) NULL,
[Date Since] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
{'/*****************'} {'Faculty Table script****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Faculty] Script Date: 19/02/26 11:11:49 AM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Faculty](
[G-Number] [char](9) NULL,
[Last Name] [varchar](20) NULL,
[First Name] [varchar](20) NULL,
[Position] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
3. Create all the primary keys shown in ER
{'/*********************Primary key on Student table**********************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Student] Script Date: 19/02/26 12:28:28 PM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[G-Number] [char](9) NOT NULL,
[Last Name] [varchar](20) NOT NULL,
[First Name] [varchar](20) NOT NULL,
[Street Num] [varchar](6) NULL,
[Street Name] [varchar](50) NULL,
[City] [varchar](20) NULL,
[State] [char](3) NULL,
[Zip] [char](5) NULL,
[SSN] [char](9) NOT NULL,
[Major code] [varchar](4) NOT NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[G-Number] 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
{'/*********************Primary key on Registration table**********************/ '}
USE [HW1-YourInitials]
GO
CREATE TABLE Registration(
[Student G-Number] [char](9) NOT NULL,
[CRN] [char](5) NOT NULL,
[regDateTime] [datetime] NOT NULL,
[Grade] [char](2) NULL,
[Status] [char](1) NOT NULL,
CONSTRAINT PK_compRegistration PRIMARY KEY CLUSTERED ([CRN], [Student G-Number]))
{'/*********************'}{'Primary key on Course Offering table'} {'**********************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Course Offering] Script Date: 19/02/26 1:07:44 PM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Course Offering](
[CRN] [char](5) NOT NULL,
[Semester] [char](2) NOT NULL,
[SemYear] [char](4) NOT NULL,
[Section] [varchar](3) NOT NULL,
[Max Students] [int] NOT NULL,
[Faculty G-Number] [char](9) NULL,
[Subject Code] [char](4) NOT NULL,
[Course Number] [char](3) NOT NULL,
CONSTRAINT [PK_Course Offering] PRIMARY KEY CLUSTERED
(
[CRN] 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
{'/*****************'} {'Primary key on Faculty Appointment Table ****************************/ '}
CREATE TABLE Faculty Appointment
(
[DEPT CODE] [char](4) NOT NULL,
[Faculty G-Number] [char](9) NOT NULL,
[Date Since] [datetime] NULL
CONSTRAINT PK_CompositePKTable PRIMARY KEY CLUSTERED ([DEPT CODE], [Faculty G-Number])
-- CONSTRAINT PK_CompositePKTable PRIMARY KEY NONCLUSTERED (column1, column2)
)
{'/*****************'} {'Primary key on Course Timings Table ****************************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Course Timings] Script Date: 19/02/26 1:35:02 PM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Course Timings](
[Schedule Code] [char](10) NOT NULL,
[Day of Week 1] [char](1) NOT NULL,
[Begin Time 1] [datetime] NOT NULL,
[End Time 1] [datetime] NOT NULL,
[Day of Week 2] [char](1) NULL,
[Begin Time 2] [datetime] NULL,
[End Time 2] [datetime] NULL,
[Room Number] [char](4) NOT NULL,
[Building Code] [char](3) NOT NULL,
[CRN] [char](5) NOT NULL,
CONSTRAINT [PK_Course Timings] PRIMARY KEY CLUSTERED
(
[Schedule Code] 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
{'/************Primary key on Faculty****************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Faculty] Script Date: 19/02/26 2:29:14 PM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Faculty](
[G-Number] [char](9) NOT NULL,
[Last Name] [varchar](20) NOT NULL,
[First Name] [varchar](20) NOT NULL,
[Position] [varchar](20) NULL,
CONSTRAINT [PK_Faculty
] PRIMARY KEY CLUSTERED
(
[G-Number] 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
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Buildings] Script Date: 19/02/26 2:37:39 PM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Buildings](
[Building Code] [char](3) NOT NULL,
[Building Name] [varchar](30) NOT NULL,
[Street Number] [varchar](4) NULL,
[Street Name] [varchar](50) NULL,
[Mail Stop Num] [varchar](3) NULL,
[City] [varchar](20) NULL,
[State] [char](2) NULL,
[Zip] [char](5) NULL,
CONSTRAINT [PK_Buildings] PRIMARY KEY CLUSTERED
(
[Building Code] 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
4. Create all unique constraints (if any)
{'/************Unique key on student table having SSN unique****************/'}
USE [HW1-YourInitials]
GO
{'/****** Object: Table [dbo].[Student] Script Date: 19/02/26 12:28:28 PM ******/'}
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[G-Number] [char](9) NOT NULL,
[Last Name] [varchar](20) NULL,
[First Name] [varchar](20) NULL,
[Street Num] [varchar](6) NULL,
[Street Name] [varchar](50) NULL,
[City] [varchar](20) NULL,
[State] [char](3) NULL,
[Zip] [char](5) NULL,
[SSN] [char](9) NULL UNIQUE,
[Major code] [varchar](4) NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[G-Number] 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
5. Create foreign keys shown in ER
CREATE TABLE Registration(
[Student G-Number] [char](9) NOT NULL References Student([G-Number]),
[CRN] [char](5) NOT NULL References Course Offering([CRN]),
[regDateTime] [datetime] NOT NULL,
[Grade] [char](2) NULL,
[Status] [char](1) NOT NULL,
CONSTRAINT PK_compRegistration PRIMARY KEY CLUSTERED ([CRN], [Student G-Number]))
CREATE TABLE FacultyAppointment
(
[DEPT CODE] [char](4) NOT NULL REFERENCES Department([DEPT CODE],
[Faculty G-Number] [char](9) NOT NULL REFERENCES Faculty([G-Number]) ,
[Date Since] [datetime] NULL
CONSTRAINT PK_CompositePKTable PRIMARY KEY CLUSTERED ([DEPT CODE], [Faculty G-Number])
6. Create all the check constraints needed
CREATE TABLE [dbo].[CourseOffering1](
[CRN] [char](5) NOT NULL,
[Semester] [char](2) NOT NULL,
[SemYear] [char](4) NOT NULL,
[Section] [varchar](3) NOT NULL,
[Max Students] [int] NOT NULL,
[Faculty G-Number] [char](9) NULL,
[Subject Code] [char](4) NOT NULL,
[Course Number] [char](3) NOT NULL,
CONSTRAINT [PK_Course Offering] PRIMARY KEY CLUSTERED ([CRN]),
CONSTRAINT [check_semyear] CHECK (semyear>=1990 and semyear<=2999))
