Database Designing

Running head: DATBASE DESIGNING
Assessment 1: Written Assessment
Student Name: Ahmed Sher
Student ID: 39832
Unit Code: ICTDB502
Unit Name: Design a database

DATABASE DESIGNING1
Q1. What is the main advantage of using Windows Authentication mode as the way I
authenticate users trying to access SQL Server?
Answer 1: Windows Authentication is a form of default confirmation mode which is
considerably safer than SQL Server Authentication. It uses Kerberos security convention which
provides key strategy implementation with respect to the intricacy approval of solid passwords,
provides assistance for lockout the record and backings the top-secret key lapse. We can generate
at the domain level utilizing the Windows authentication as well as login or the user can be
produced on SQL Server for the group. While connecting the database by Windows
Authentication, the SQL Server authenticates name of the account as well as Windows
authenticate the password by the Windows client account or the identity of the user. The SQL
server does not appeal secret key or any sort of user validation.
Q2. Give two examples of predefined server roles in SQL Server?
Answer 2:
SERVER ROLE DESCRIPTION
processadmin People that are assigned in the role of processadmin will be able to
complete all the methods which are processing in the SQL server.
sysadmin People of this department are able to operate any sort of operation in the
server.

DATABASE DESIGNING2
Q3. Write down the SQL command I would use to assign a user called Fred to the
Marketing role in SQL Server?
Answer 3:
ALTER ROLE Marketing_Role ADD MEMBER [Domain\ Fred] ;
GO
Q4. What are the three recovery modes available for a database in SQL Server?
Answer 4:
The following are some recovery models of the SQL server of a database.
 Simple
 Bulked Load
 Full
Q5. You have proposed the following design for a CUSTOMER table to a client (see SQL
statement defining the CUSTOMER table below). The client points out that, for his
business, customer identifiers can have letters as well as numbers e.g. MR12345 is a valid
customer id, and also points out that we need to store the customer’s date of birth. Write a
new CREATE TABLE statement for the Customer table that addresses the concerns of this
customer by defining a CUSTOMER table that will suit the client’s expressed needs.
CREATE TABLE [dbo].[Customer]( [CustomerId] [int] NOT NULL, [Fname] [varchar]
(10) NULL, [Sname] [varchar](10) NULL, CONSTRAINT [PK_Customer] PRIMARY
KEY CLUSTERED
Answer 5: