Reference no: EM133189057

Schema Control and Administration

Description:
This assessment will make use of a central narrative (case study) file, please note that this case study may be unique to your intake, as such the answers generated will be exclusive to your cohort ensuring a customized experience and supporting academic integrity initiatives.
This assessment has multiple objectives for student learning and skill(s) development:
1) This scenario will provide students with a hands-on work-related case which is meant to;
a. Reinforce an understanding as to how IT roles and technology support business functions and non-IT employees
b. Depict forms and approaches to data integration

2) This deliverable will afford course faculty a chance to gauge student preparedness and ability to;
a. To understand directions provided in a workplace setting
b. To work within a business setting producing work that fits the set requirements of an employer

3) This assessment is part of a series of deliverables that make use of a continuing narrative, which affords students the opportunity to experience first-hand how any project can leverage expertise from multiple different fields to properly develop a functional solution.

Tasks:
Your submission for this assessment will be formatted as an informal written report responding to your employer's work request(s). You are to address the following emails while referencing the client organization's Employee Database.

Obtain, or create, a copy of the Employee Database, if you have previously built the Employee Database in a previous project then you may reuse said database on this individual assessment.

I'm not sure if anyone has briefed you on this, the department has been working towards standing-up an intranet site to allow select employees the ability to pull data from the Employee Database that is in your administrative portfolio. The project is meant to reduce the departments dependence on submitting data requests to you and the other Database Administrators, simplifying the process by allowing direct access to relevant by different groups of internal users.

We just wrapped up our meeting with the web developers, and before they can implement the employee page for our intranet site, they had a few observations about our employee database that we need to implement to meet data protection regulations. From what I could gather, there are a few key elements that we need to get into place now before any more work can be done on the system, as always, we must affect as little changes to the system as possible as there are a number of systems and applications already running off of the employee database and we can't afford to take it offline right now. The first thing that the dev's requested was that we needed to establish proper schemas for the database. For the schemas, we were looking at five general groupings for the tables:
• Schema 1; Include any table that has general information about the employee and their role.
• Schema 2; Include tables that have private or personal data which would be subject to a higher degree of governmental regulations. I don't really think we should let many people have access to the data on these tables, the last thing we need is a lawsuit.
• Facility schema; any non-personal data that doesn't contain address information should be dumped into this schema. It doesn't matter if the tables aren't really a perfect fit with ‘facility' data, this will more-or- less just be a catch all for any table we can't place inside any of the other schemas.
• Schema 4; Anything to do with contact information must be grouped together in a single schema. This schema will need to be restricted to only certain roles, there is no need for everyone in the organization to be able to see into this subset of data.
• Address schema; this one is pretty self-explanatory.
You should also probably come up with clear names for the schemas for whichever ones we haven't named yet. No need to ask for approval, just call them whatever makes sense. The developers asked if you can forward the SQL commands you used to create all of the schemas and assign the tables within each of their respective schemas.

Thanks for looking into establishing all the database schemas and the administrator account.
We're currently preparing to build account access on the intranet site but first need you to add some user accounts to the database so we can test out the connection on our end. We haven't decided yet exactly which employees we will be granting access too though, so for the time-being, I need you to setup some generic dummy accounts for a few of the departments. Just name the generic accounts after the job roles, we will later use these as templates for actual employees.
The web development team sent me the following to forward to you:
• Create database users [sql server authentication] with passwords, no need to connect them to credentials or certificates.

• You can create the users with any process you choose, just be sure to document how you did it and email it back to me, like a step-by-step tutorial. That way we can have some of the junior DBAs on staff replicate your work for specific user accounts when we're ready, we don't want to bother you with the busy work.
• We will need the following for the user templates [users];
o Human Resources, must have access to schema 1, 2, and 4. They'll be in-charge of making all updates that are necessary, so give them full permissions.
o Accounting, need access to Schema 1, and the Address Schema, they'll need to pull data to know where to send paystubs and tax forms, so assign the permissions accordingly.
o Sales, who will need access to the Facility Schema, and Address Schema, they require read-only access, they don't need to modify any records, so assign restrictive permissions.

We also want to test out views, and the websites ability to pull data from them, so setup a view to produce a list of all employees whom have not yet successfully completed their probation period assigned to their job role using the Roles and Employee table.

Attachment:- Schema Control and Administration.rar