CREATE TABLE [dbo].[Nurse](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [WardId] [int] NOT NULL,
 CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [StaffNumber] [nvarchar](50) NOT NULL,
    [Salary] [money] NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [GenderId] [int] NOT NULL,
    [ContactNumber] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_Employee] 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] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,        -- why not name this EmployeeId ?
    [Name] [nvarchar](100) NOT NULL,
    [StaffNumber] [nvarchar](50) NOT NULL,
    [Salary] [money] NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [GenderId] [int] NOT NULL,
    [ContactNumber] [nvarchar](20) NOT NULL,
  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
    ( [Id] )
 ) ;

CREATE TABLE [dbo].[Nurse](
    [EmployeeId] [int] NOT NULL,                 -- not IDENTITY !
    [WardId] [int] NOT NULL,
  CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED
    ( EmployeeId ),
  CONSTRAINT [FK_Employee_Nurse] FOREIGN KEY
    ( EmployeeId )
    REFERENCES dbo.Employee ( Id )
) ;

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [StaffNumber] [nvarchar](50) NOT NULL,
    [Salary] [money] NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [GenderId] [int] NOT NULL,
    [ContactNumber] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [Id] ASC
))

GO
CREATE TABLE [dbo].[Nurse](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] FOREIGN KEY REFERENCES EMPLOYEE(ID),
    [WardId] [int] NOT NULL,
 CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED
(
    [Id] ASC
))
GO
CREATE UNIQUE INDEX UQ_NURSE_EMPLOYEEID ON dbo.Nurse ( EmployeeId )