Allow primary primary ansi offgo
CREATE DATABASE HW1-YourInitials
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = HW1-YourInitials)
/*****************Student Table script****************************/
USE [HW1-YourInitials]
SET QUOTED_IDENTIFIER ON
GO
[Last Name] [varchar](20) NULL,
[First Name] [varchar](20) NULL,
[Zip] [char](5) NULL,
[SSN] [char](9) NULL,
GO
/***************** Registration Table script****************************/
GO
SET QUOTED_IDENTIFIER ON
[Student G-Number] [char](9) NULL,
[CRN] [char](5) NULL,
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[Registration] Script Date: 19/02/26 10:45:59 AM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[Registration](
[Status] [char](1) NULL
) ON [PRIMARY]
USE [HW1-YourInitials]
GO
GO
SET ANSI_PADDING ON
[SemYear] [char](4) NULL,
[Section] [varchar](3) NULL,
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[Course Timings] Script Date: 19/02/26 10:56:46 AM ******/
SET ANSI_PADDING ON
GO
[End Time 1] [datetime] NULL,
[Day of Week 2] [char](1) NULL,
[CRN] [char](5) NULL
) ON [PRIMARY]
USE [HW1-YourInitials]
GO
GO
SET ANSI_PADDING ON
[Seating Capacity] [int] NULL,
[Computer] [bit] NULL,
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[Buildings](
[Building Code] [char](3) NULL,
[City] [varchar](20) NULL,
[State] [char](2) NULL,
GO
/***************** Department Table script****************************/
GO
SET QUOTED_IDENTIFIER ON
[DEPT CODE] [char](4) NULL,
[Dept Name] [varchar](30) NULL
/***************** Faculty Appointment Table script****************************/
USE [HW1-YourInitials]
SET QUOTED_IDENTIFIER ON
GO
[Faculty G-Number] [char](9) NULL,
[Date Since] [datetime] NULL
/***************** Faculty Table script****************************/
USE [HW1-YourInitials]
SET QUOTED_IDENTIFIER ON
GO
[Last Name] [varchar](20) NULL,
[First Name] [varchar](20) NULL,
GO
3. Create all the primary keys shown in ER
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[Student](
[G-Number] [char](9) NOT NULL,
[City] [varchar](20) NULL,
[State] [char](3) NULL,
(
[G-Number] ASC
GO
/*********************Primary key on Registration table**********************/
[CRN] [char](5) NOT NULL,
[regDateTime] [datetime] NOT NULL,
USE [HW1-YourInitials]
GO
GO
SET ANSI_PADDING ON
[SemYear] [char](4) NOT NULL,
[Section] [varchar](3) NOT NULL,
CONSTRAINT [PK_Course Offering] PRIMARY KEY CLUSTERED
(
SET ANSI_PADDING OFF
GO
[Faculty G-Number] [char](9) NOT NULL,
[Date Since] [datetime] NULL
USE [HW1-YourInitials]
GO
GO
SET ANSI_PADDING ON
[Begin Time 1] [datetime] NOT NULL,
[End Time 1] [datetime] NOT NULL,
[Building Code] [char](3) NOT NULL,
[CRN] [char](5) NOT NULL,
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[Faculty] Script Date: 19/02/26 2:29:14 PM ******/
SET ANSI_PADDING ON
GO
[Position] [varchar](20) NULL,
CONSTRAINT [PK_Faculty
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[Buildings] Script Date: 19/02/26 2:37:39 PM ******/
SET ANSI_PADDING ON
GO
[Street Name] [varchar](50) NULL,
[Mail Stop Num] [varchar](3) NULL,
(
[Building Code] ASC
GO
4. Create all unique constraints (if any)
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[Student](
[G-Number] [char](9) NOT NULL,
[City] [varchar](20) NULL,
[State] [char](3) NULL,
(
[G-Number] ASC
GO
5. Create foreign keys shown in ER
[Grade] [char](2) NULL,
[Status] [char](1) NOT NULL,
[Faculty G-Number] [char](9) NOT NULL REFERENCES Faculty([G-Number]) ,
[Date Since] [datetime] NULL
[Semester] [char](2) NOT NULL,
[SemYear] [char](4) NOT NULL,
[Course Number] [char](3) NOT NULL,
CONSTRAINT [PK_Course Offering] PRIMARY KEY CLUSTERED ([CRN]),