Chapter 1
Major Topics for Database:
1. Software
2. Software version
3. license agreement
4. machine
5. machine location
6. install date
7. software requests
8. availability
9. software location
10. access level (by who, e.g., student, teacher, admin user, etc.)

STATEMENT OF WORK [Draft]
History
The law requires that any business, including a school, track its software. It is important to know what software the school owns, in what versions, and what the license agreement for that software is. Whatever the license agreement for particular software, it is essential for the institution to know which software is installed on which machine, where that machine is located, and which users have access to that machine. It is also important to track when the software is uninstalled from a machine and when a machine is retired. Grandfield college currently uses several spreadsheets to track this information. They also track requests for new software, software checkouts to faculty with administrative privileges on their machines, and if installs are successful or run into any problems.
When requests for software are received, the software management team must manually access a spreadsheet to see if the school has the data. When new software arrives, they must manually check the licensing agreement online when they don’t know off the top of their head.
These agreements vary between being accessible to any computer on site via a server install or having a limited number of copies that must be installed client-side.
A database would allow Grandfield to effectively track all of these things in one place and to be able to draw a better view of what the demands are over time.

Scope
Grandfield College wants a database to track faculty and staff computers, what software is installed on each, who has access to each computer, and requests for new software. Being able to track install issues would also be of great benefit for easier troubleshooting.

Constraints
Software for student machines is a separate and complex issue and will be treated as a separate project at a later time.

Objectives
●
●
●

Track what software is installed and removed and the licensing and type of install (local vs. server) for the software on faculty and staff computers in one place.
Track the location of the faculty and staff computers and who has access to each.
Track requests for new software and when software is checked out to faculty with administrator privileges on their machines.

Tasks and Timeline
1. Gathering Data: This task will consist of obtaining the spreadsheets that contain the lists of the listing of software, the type of licensing for the software, and all of the faculty and staff computers and the software installed on each. Also, list of software requests.
Time allotted: 3 weeks.
Deliverables: A list of scheduled interviews and observations and text of the questionnaires. 2. Analyzing Data: The data gathered will be analyzed to determine preliminary data modeling.
Time allotted: 2 weeks.
Deliverables: List of business rules. Their basic entities and attributes. To be reviewed.
3. Normalization: The data model will be completed with entities and relationships normalized.
Time allotted: 1 week.
Deliverables : Entity relation diagram for review.
4. Building the physical database: The data model will be translated to the RDBMS. Tables containing columns with specific data types and relational and other constraints created.
Time allotted: 3 days.
Deliverables: The schema of the database for review.
5. Testing and security: sample data will be entered, and each of the business rules and requirements will be tested. General database security and security related to business rules will also be tested.
Time allotted: 3 weeks.
Deliverables : Documented test results.
6. Database Completion and Installation: Final changes and corrections are made. Sample data will be removed, and the database installed on a server. Final testing for server access and connections. Time allotted: 2 weeks.

Deliverables: The working database.

Total time between beginning of project and end: x weeks, x days.

Chapter 2
1. Questions about data
1. Is there documentation by software on how many times a piece of software has been installed/uninstalled? 2. Location: how do you track classroom machines vs. office machines
3. What does CCS Number mean?
4. Who is the pricing unit important to? What is the data used for?
5. Is every license per computer or some variable? (i.e. multi-core computers)
a. Site Licenses - can’t leave the site
b. User Licenses - by number users
c. machine licenses - by # machines running at a time
d. open source - no limit

2. Stakeholders:
● Software Management Team
● Faculty / Staff
● School Administration
● Clients
● Software companies
3. Plan for interview with stakeholders (one hour)
Find out what makes everyday tasks difficult for staff and find a solution.
Software Management Team:(30 minutes)
1. Are settings documented in any way to help troubleshoot future install issues? useful to have, not required
2. Who is responsible for updating which software the school has and the version and licensing? -techs
3. Where and how are the number of copies installed tracked for limited licenses? should be
4. Who has permissions to purchase new licenses and how are these documented?
-administrators...
5. Is there a place that staff/faculty can view available software? -optional

6. What software / software versions are required on all school owned machines? OSs have to be the same
7. How are updates handled? -don’t need it right away
8. Which software is installed from the network drive and which must be installed from disk? Is this tracked anywhere? -should be tracked
9. Are administrative privileges on machines tracked? should be tracked, doesn’t have to be.
10. Is there privileged access to certain data (if so which data and what does it concern) - security isn’t a huge concern
11. Is there privileged access to certain software i.e. how is need for certain types of software evaluated? (Design programs vs. programming programs) -anyone can if they have some reason
Faculty / Staff: (25minutes)
1. How do you currently request software installation / removal?
2. When software fails to work, how is the issue resolved?
3. How do you check if a machine you will be using is updated with the software you need?
4. What you like the most and the least about the current system?
Administration: (5minutes)
1. How are requests for new software evaluated?
2. Who is the Admin(s)?
3. Who is the pricing unit important to? What is the data used for?

4. Changes faculty and staff would like to see in request process
○ What do you like and dislike about the current software request process?
○ What would make this process easier?
5. Exceptions, additional business rules, additional questions
○ Exception: Faculty that has administrative privileges on their machine that still uses the school’s software.
○ Are settings documented in any way to help troubleshoot future install issues?
○ Is there any documentation for errors currently?
○ What kind of security is implemented to secure the databases / software / servers? ○ Expectations of future growth?
○ Can faculty with administrative privileges install from the server?

Chapter 3

List of issues with the current system
● It is difficult to track which software is on which machine.
● It can be difficult to get/track licensing agreement information.
● It can be difficult to fulfill requests for new software licenses.
● It is difficult to track errors.
List of database requirements for each stakeholder
Staff/Faculty
● Allow faculty to see software that can be installed via the servers so that they can install it on their own.
● Allow faculty to make requests for SMT to install new software that can’t be installed via server.
● Allow faculty to see a list of all licenses before making requests for new software.
● Allow faculty to make requests for new software licenses.
Software Management Team (SMT)
● Allow SMT to check licensing agreements
● Allow SMT to track software installation/uninstallation and available copies for faculty ● Allow SMT to check / update / remove administrative privileges / who has access to machines.
● Allow SMT to document machine configuration
● Allow SMT to record installation issues
● How will everything be secured
● Allow SMT to track pending installations
● Allow SMT to check requests for software
School Administration
● To track licensing agreements - what they’ve purchased vs. how many are installed. ● Track costs associated with purchasing new licensing rights / repurchasing licensing rights.
List of business rules
● Which software will require support from Grandfields IT support or the software companies responsibility
● Faculty with administrative privileges do not have support from SMT
● Can only run software installed on the computers. If users say they need special software, perhaps from home, they must ask the IT admin(s).
● How requests are tracked.
● How installs are tracke
Security Rules
● Only admins with correct privileges can assist with any installing or system modification.

●
●

Minimum access granted. If they aren’t suppose to enter data to a specific field then they don’t have the privileges.
Keep minimum and maximum 2 - 3 admins employed. Let admins delegate certain permissions and privileges to other employees but never give full control.

Entities / Attributes
Entity

Attribute

Software Program

Name
Version
Company
Installation Type (local vs. server)
License
Minimum requirements?

Installs

Date
Time Spent
Successful or Not
Installation Issues
Machine
Software
Faculty

License

Type
Start Date
End Date
Terms
Pricing
Pricing Unit

Machine

ID / Asset Number - CCS
Installed Programs
Install Date
Users
Configuration
Location
Administrative Privileges
OS

[User]

Privileges
Location
First Name
Last Name
Email
Faculty ID

Requests

Requester
Request Date
Requested Software
Reason for request
New or Existing License
Response
Response Date
Status

Candidate Keys

Software Program
● Name / Version
Installs
● Surrogate key
License
● Surrogate key
Machine
● CCS
User
● ID #
Requests
● Surrogate key
Entity Roles:
Domain

Software, Machine, User, Request

Linking

RequestSoftwareUser, UserMachine

Lookup

License

Weak

Install

Chapter 4 /5

Chapter 6 mySQL syntax:
***********************Basic Syntax to Reference******************************
1. CREATE TABLE EmployeeKey
2. (
3. EmployeeKey NVarChar(255),
4. ProjectKey int,
5. CONSTRAINT fk_Employee FOREIGN KEY(EmployeeKey)
6. REFERENCES Employee(EmployeeKey),
7. CONSTRAINT fk_Project FOREIGN KEY(ProjectKey)
8. REFERENCES Project(ProjectKey),
9. CONSTRAINT PK_EmployeeProject PRIMARY KEY(EmployeeKey,ProjectKey)
10. )
********************************************************************************
Below Is The Syntax That Successfully Complied Used In Our Database
*********************************************************************************
GO
Use Granfield
GO
CREATE TABLE License
(
LicenseKey int identity(1,1) Primary Key Not Null,
LicenseType NVarChar Not Null,
LicenseStart Date Not Null,
LicenseTerm NVarChar Not Null,
LicenseEnd Date Not Null,
LicenseRegistrationKey nvarchar(50) Not Null,
)
CREATE TABLE Software
(
SoftwareKey int identity(1,1)Primary Key Not Null,
SoftwareName NVarChar Not Null,
SoftwareVersion NVarChar Not Null,
SoftwareManufacturer NVarChar Not Null,
SoftwareInstallType NVarChar Not Null,
SoftwareLicence NVarChar Not Null,
LicenseKey int Not Null,

CONSTRAINT fk_LicenseKey FOREIGN KEY(LicenseKey)
REFERENCES License(LicenseKey),
)

CREATE TABLE Install
(
InstallKey int identity(1,1) Primary Key Not Null,
InstallDate datetime Not Null,
InstallMachine NVarChar Not Null,
InstallSoftware NVarChar Not Null,
InstallSuccesful NVarChar Not Null,
SoftwareKey int Not Null,
CONSTRAINT fk_Software FOREIGN KEY(SoftwareKey)
REFERENCES Software (SoftwareKey),
)

CREATE TABLE Machine
(
MachineCSS int identity(1,1) Primary Key Not Null,
)
CREATE TABLE [User]
(
UserID NVarChar(64) Primary Key Not Null,
UserFirstName NVarChar Not Null,
UserLastName NVarChar Not Null,
)
CREATE TABLE UserMachine
(
UserMachineID int identity(1,1) Primary Key Not Null,
MachineCSS int Not Null,
UserID NVarChar(64) Not Null,
CONSTRAINT fk_Machine FOREIGN KEY(MachineCSS)
REFERENCES Machine(MachineCSS),
CONSTRAINT fk_User FOREIGN KEY(UserID)
REFERENCES [User](UserID)
)
CREATE TABLE [Request]

(
RequestID int identity(1,1) Primary Key Not Null,
RequesterFirst NVarChar Not Null,
RequesterLast NVarChar Not Null,
RequestDate Date Not Null,
RequestSoftware NVarChar Not Null,
RequestReason NVarChar Not Null,
RequestStatus NVarChar Not Null,
)
CREATE TABLE RequestSoftwareUser
(
UserRequestID NVarChar Not Null Primary Key,
UserID NVarChar(64) Not Null,
RequestID int Not Null,
CONSTRAINT fk_UserID FOREIGN KEY (UserID)
REFERENCES [User](UserID),
CONSTRAINT fk_RequestID FOREIGN KEY (RequestID)
REFERENCES Request(RequestID),
)
**************************************************************************

Screenshots of Data

Chapter 7
1.Two or three simple SELECTs with various WHERE criteria
SELECT SoftwareName, SoftareVersion
FROM Software
WHERE LicenseKey IS NOT NULL
SELECT MachineCSS, MachineOS, LocationKey

FROM Machine
WHERE LocationKey = 5
2. Two or three queries using aggregate functions
SELECT MachineCSS, COUNT(InstallationKey) AS [Total Installs]
FROM Install
GROUP BY SoftwareKey
SELECT LicenseKey, COUNT(SoftwareKey) AS [Licenses]
FROM Software
GROUP BY SoftwareKey
3. At least two queries that use joins
SELECT MachineCSS, LocationKey, SoftwareName
FROM Machine M
INNER JOIN Software s
ON s.SoftwareKey = m.SoftwareKey
SELECT MachineCSS, LocationKey, SoftwareName, InstallationDate,
InstallationNoteText
FROM Machine M
INNER JOIN Software s
ON s.SoftwareKey = m.SoftwareKey
INNER JOIN Install i
ON i.SoftwareKey = s.SoftwareKey
INNER JOIN InstallationNote n
ON n.InstallationNoteKey = i.InstallationNoteKey
WHERE i.SoftwareKey IS NOT NULL
4. Two or three INSERT statements
INSERT INTO Request(
UserID,
RequestSoftware,
RequestReason,
RequestNoteText)
VALUES (‘Sconge’,
‘VisualBasic’,
‘Upgrade’,
‘New Version is now available’)
INSERT INTO Software(
SoftwareName,
SoftwareVersion,

SoftwareManufacturer,
SoftwareInstallationType,
LicenseKey)
VALUES (‘Illustrator’,
‘Creative Suite 5’,
‘Adobe’,
‘Disc’
‘1’)

5. One or two UPDATEs and/or a DELETE
UPDATE request SET requestResponse = 'approved', requestStatus='approved'
WHERE requestID='2'
DELETE FROM software
WHERE softwareName = 'Illustrator'
AND fk_licenseKey = '1'

Chapter 8
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and authorization and general policies and procedures. Consider the use of roles, stored procedures, views, and other tools.
Staff/faculty
Table name

Select

user

Update

x

software

Insert

x

license machine x

location

x

install installNote requestNote

x

x

request

x

x

Delete

Constraints

userMachine

x

Software Admin
Table name

Select

Insert

Update

user

x

x

x

x

x

x

x

x

x

location

x

x

x

install

x

x

x

installNote

x

x

x

requestNote

x

x

x

request

x

x

x

userMachine

x

x

Constraints

x

machine

Delete

x

license

Constraints

x

software

Delete

x

Database administrator

Table name

Select

Insert

Update

user

x

x

x

x

software

x

x

x

x

license

x

x

x

x

machine

x

x

x

x

location

x

x

x

x

install

x

x

x

x

installNote

x

x

x

x

requestNote

x

x

x

x

request

x

x

x

x

userMachine

x

x

x

x

2) Threat Analysis
Staff/faculty threats
Threat

Description

Select

-Select private user information

Insert

-Accidental or malicious entry

Update

-

Delete

-

Software Admin threats
Threat

Description

Select

-

Insert

-Data entry mistakes can compromise data integrity

Update

-Accidental updates
-Missing where criteria

Delete

-

Database administrator threats

Threat

Description

Select

-

Insert

-

Update

-Accidental updates
-Missing where criteria

Delete

-Accidental deletion of records

3) Database roles db_staff -Grants the user permission to SELECT information on customer -Grants the user permission to INSERT customer information

db_softwareAdmin

-Grants the user permission to select all tables and views in the database
-Grants the user permission to insert and update all tables in the database

db_owner

Grants ownership and full permissions on all database objects. Policies
-database server will have two separate physical drives
-drives will be stored off-site in a secure site
Backup procedure
-four portable hard drives
-perform a full database backup daily alternating dated backup drive
Recovery procedure
-restore database from most recent backup
-attempt to recover lost data
-backup
Authorization and Authentication
Every user will require a unique login. Each login will be assigned a role. Each role will have stored procedures and views that are executed via web forms.