Search results for 'cis 515'

Build queries through SQL along with the query-building tool with sales databases built in Week Two for Riordan Manufacturing. Create a separate table for query information. Include the query name and query description. Enter data into the fields, naming and describing as many queries as you can, and then write two thirds of the queries using the query-building tool. Write the remaining queries in SQL. Create a new query without the tools and enter the SQL into its view. Test your queries to verify that they return the data you described. Learn More

In your lab for this week you are going to work with three different areas and processes within the Oracle Database that can be used to control data security. Each of these three processes has its own distinctive application to providing levels of security. In each case the individual processes deal with either limiting a users access to the database, limiting access to processes within the database, or keeping track of what the user is doing while in the database.

For the lab you will be using the SCOTT user which is already created in your instance. In Step 4 you will also be asked to shutdown you instance, make some edits to the init.ora file for your instance and then restart the instance. If you are not comfortable with this process which was first introduced to you in DBM438 the refer to the iLab Manual found in week 1 for guidance.

To record your work for this lab use the LAB5_Report.doc found in Doc Sharing. As in your previous labs you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Dropbox for Week 5.

LAB STEPS

STEP 1: Define a New Profile

Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times. In this section of the lab we are going to create a custom profile that will then be applied to the SCOTT user.

1. To begin, log into your instance as the SYS user.
2. Write SQL script that will create a new profile named DBM449_SCOTT_PROFILE that will do the following:
• Limit the number of failed login attempts to 3 in a row.
• Limit the overall connection time to 10 hours (we will give him a little leeway incase he has to work overtime).
• Allow a session to be idle no more than 1 hour.
• Change the password every 60 days.
• Allow the user 3 days to change the password after it expires.
• Not allow a previous password be reused before there have been three password changes.
3. Execute your pfile script and verify that the profile has been created by running a query against the DBA_PROFILES view in the data dictionary. Limit your output to ONLY the DBM449_SCOTT_PROFILE parameters.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 2: Testing the New Profile

Now that we have a new profile for the SCOTT user we need to verify that it works properly. For obvious reasons there are going to be parts of the profile that we cannot test within the confines of this lab due to time constraints, but we can test to verify that the SCOTT user is being controlled by the profile.

1. The first thing we need to do is assign the profile to the SCOTT user. While still logged into your instance as the SYS user write and execute the SQL command that will assign the new SBM449_SCOTT_PROFILE profile to the SCOTT user.
2. Now log into SCOTT (password is TIGER). Remember that you must supply the database instance name when logging in from the SQL> prompt just as you do when using the login window, i.e. CONN SCOTT/TIGER@DB####.WORLD.
3. There are several things that we can test related to the logging in and changing a password so here we go.
• You should now be successfully connect to the SCOTT user. Write the connect command again on this time use an incorrect password. NOTE: you should get a warning message stating that you are no longer connected to Oracle. That is fine, just keep trying to log in.
• Repeat the above process until you get the ORA-28000: the account is locked error which will indicate that the profile is working here.
• At this point we need to get the account unlocked so you will need to login to your instance as the SYS user and unlock the SCOTT account BUT DO NOT LOG BACK INTO THE SCOTT USER YET.
• Now we can test the password reuse parameter. To do this we must EXPIRE the current password. Write and execute the SQL command to expire the password for the SCOTT user.
• Now log back into the SCOTT user. You should receive a message stating that the password has expired (ORA-28001: the password has expired) and then prompting you to change the password.
• Try to reuse the TIGER password. You should receive the following - ORA-28007: the password cannot be reused.
4. Now log into the SCOTT user again and this time change the password to LION to complete this step of the lab.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 3: Using the PRODUCT_USER_PROFILE table

As the owner of a schema a user has certain inherited privileges that would allow the user to pass access to his/her own objects on to other users. Often times this can open up data to scrutiny by individuals who probably do not need to have access to it. These types of decisions should always be made by the DBA in charge of the database. One mechanism the DBA has to keeping users from using these inherited privileges is by excluding those commands using the PRODUCT_USER_PROFILE (PUP) table. In this section of the lab we are going to do this to the SCOTT user by setting up the scenario that will prohibit him from giving the user GEORGE (created in lab 2) access to the EMP table.

1. For this section and remainder of the lab you must have the PRODUCT_USER_PROFILE successfully loaded and accessible in your instance. The creation of this profile was one of the first things done back in Lab 1 when you ran the PUPBLD.SQL script. If you are getting an error message stating "Error accessing PRODUCT_USER_PROFILE" when you log in as the DBM449_USER or the SCOTT user then this profile is not successfully installed. Work with your instructor to figure out why your script from Lab 1 did not work correctly. Until this is resolved you will not be able to complete the remainder of the lab.
2. If you have the PRODUCT_USER_PROFILE successfully working then log in to your database instance as the SYS user.
3. Now we need to limit SCOTT from being able to use the GRANT command.
• Insert the proper values into the PRODUCT_USER_PROFILE table that will keep the SCOTT user from using the GRANT command. Remember that some of the values in your insert statement must be in upper case and some will need to be in mixed case. Once you have done this then query the table to verify the insert (REMEMBER: you cannot query the table as the SYS user, only as the SYSTEM user).
• Now we need to test our above settings and make sure they are working.
• Connect to the SCOTT user (remember that you changed the password to LION).
• Write and execute the statement that would GRANT the user GEORGE the ability to write a select statement and see the data in the EMP table owned by SCOTT. You should receive the following message - SP2-0544: Command "grant" disabled in Product User Profile.
4. This verifies that you have now disabled the ability of the SCOTT user to allow another user to access any of the data in his schema.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 4: Setting up the Database to use Auditing

Being able to audit what, when and where people are doing things in the database can be a very enlightening thing for a DBA. It can also be a very important tool in working with Data Security. Oracle provides the ability to do various types of auditing, but it takes some special setting up of the environment for this to work. In this step we are going to make the necessary adjustments to the current Oracle instance so that we can enable auditing and make some tests. If you need to review the processes to be used here then refer to the iLab Manual in week 1.

1. First you need to make sure that you are logged into your instance as the SYS user.
2. At this point issue a SHUTDOWN IMMEDIATE command to shut down you database instance.
3. Once the instance is shut down you need to go into your Citrix Windows Explorer application, find your database instance set of directory folders, drill down to the pfile directory folder and open your init.ora file found in that folder.
4. Under the section titled "Security and Auditing" you need to add the parameter AUDIT_TRAIL and set the parameter to DB_EXTENDED. This will allow the SQL_TEXT column of the DBA_AUDIT_OBJECT view to be populated. Save and close the file and then go back to your SQL*Plus session.
5. Now using the init.ora file, start your instance back up to an OPEN status. You can do this by issuing a STARTUP PFILE= statement and pointing to your init.ora file.
6. Once you have completed this process you are ready to begin setting up the database to audit some activity.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 5: Creating an Audit Trail

Oracle permits audit trails to be generated for session login attempts, access to objects, and activity performed on objects. Again using the SCOTT user we are going to set up several scenarios for auditing what SCOTT does while in a session. NOTE: if you need to work through this process several times you can delete the values in the AUD$ base table by issuing the TRUNCATE TABLE AUD$ command while logged in as the SYS user.

1. Make sure that you are connected as user SYS.
2. Display value of the parameter AUDIT_TRAIL. For the VALUE column you should have a value of DB_EXTENDED.
3. Now we can set up auditing to track what goes on in the database.
• Write SQL statements to audit successful and unsuccessful login attempts by SCOTT.
• Write SQL statement to audit any successful INSERT, UPDATE or DELETE performed on table DEPT in scott's schema.
4. Now we need to test the audits to verify that they work.
• Log into the SCOTT user (remember that the password is LION) and perform the following:
• write and execute an UPDATE statement that will change the value in the LOC column of the DEPT table to MIAMI where the DEPTNO value is 10.  Be sure to issue a COMMIT.
• Write and execute the INSERT statement that will in insert the following values into DEPT - (50, 'LEGAL', 'HOUSTON').  Be sure to issue a COMMIT.
• Write and execute the DELETE statement that will delete the row from the DEPT table that was just inserted in the step above.  Again, be sure to issue a COMMIT.
• Try to reconnect to the SCOTT user with an invalid password.
• Now connect back to the SYS user.

Now we need to see if our auditing worked.

1. While logged into your instance as the SYS user, query the DBA_AUDIT_OBJECT view of the data dictionary for the user name of the account (Not the OS), the object owner, the object name, the action name and the SQL command (text) from the DBA_AUDIT_OBJECT view in the Data Dictionary.
2. Did you notice that the entries for successful logon and unsuccessful logon attempts were not there. Now query the user name, action name and return code values in the DBA_AUDIT_SESSION view. You should find that information here.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

Deliverables

Submit your completed Lab 5 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.

Learn More

CIS 355 Term Project Part I For your term project, you are expected to design and implement a relational database to meet the requirements described in the PCI Warranty Call Center Case. Deliverables Part I - Project Design Document This document should have the following components: 1. A conceptual ER model/diagram of PCI’s data requirements. The diagram should include all relevant entities, attributes and relationships. For each entity, specify the identifier (primary key). Specify relationship names and cardinality constraints. Indicate which attributes are required, composite, multi- valued, and/or derived (Note: by default, an attribute is assumed to be optional, simple, single-valued and not derived). Indicate which entities are associative. Follow consistent naming conventions for entities and attributes. Use modeling/diagramming software to create the ER Diagram. 2. A list of the normalized relations (the logical model) and their attributes. For each relation, primary and foreign keys should be clearly indicated. Note: Use the format that we will be discussing in class for presenting your logical model. 3. A list of assumptions (if any) made about the information requirements presented in the case. Note: the assumptions should be reasonable and should not contradict the facts of the case. 4. A data dictionary that defines the metadata for the logical model. The data dictionary should include: the definition of each relation and attribute; the primary and foreign keys in each relation; attribute data types and lengths; and whether attributes are optional or required. Organize the data dictionary alphabetically by relation name. Assessment Part I deliverables will be evaluated based on the completeness and correctness/accuracy of the conceptual and logical data models, and of the supporting documentation (i.e., data dictionary, assumptions (if any)). If any of the deliverables are hand-drawn/written, your submission will not be graded. Submit the deliverables as one or more files. Include your name and title of the project on every page of the documents you submit. Learn More

Case Problem 2
Data File needed for this case problem: exercise.accdb (cont. from Tutorial 10)

Parkhurst Health & Fitness Center Martha Parkhurst asks you to continue your work on the Exercise database by creating a new form and enhancing its accuracy and appearance. To help Martha with her request, complete the following steps:

1. Open the Exercise database located in theAccess3\Case2 folder provided with your Data Files.

2. Use the Form tool to create a form named frmMemberinfo using the tblMember table as the source table. Create a procedure for the frmMemberInfo form to convert City field values to proper case—capitalize the first letter of each word, and convert all other letters to lowercase. Test the procedure.

3. Create a procedure to verify Phone field valuesin the frmMemberInfo form by doing the following:
a. For a State field value of VA, the first three digits of the Phone field value must equal 703 or 804. If the Phone field value isinvalid, display an appropriate message, cancel the event, undo the change, and move the focus to the Phone field.
b. No special action is required for other Phone field values.
c. Test the procedure, and then save your form changes.

4. Create a procedure for the frmMemberInfo form to do the following:
a. Display the word current to the right of the MemberID text box in bold, magenta text only when the MembershipStatus field value is Active. Otherwise, display the
word review in bold, blue text. (Hint: Remove all controlsfrom the control layout before resizing the MemberID text box and adding the label for the message. Use the Caption property in your VBA code, and make sure you enclose Caption property settings in quotation marks.)
b. Test the modified form, and then save your form changes.

5. Make a backup copy of the database, compact and repair the Exercise database, and then close the database.

Learn More

COMP353/1 Assignment #3

Exercise #1 (10 points)
Here are the two sets of FDs for R {A, B, C, D, E}.
S = {A->B AB->C D->AC D->E} T = {A->BC D->AE}
Are they equivalent?

Exercise #2 (10 points)
Consider the following decomposition of the table ENROLLMENT in two tables Student and Course.

Table ENROLLMENT
StudentID StudentName CourseName Credits
1111111 William Smith COMP218 4
2222222 Michel Cyr COMP353 4
3333333 Charles Fisher COMP348 4
4444444 Patricia Roubaix COMP353 4
2222222 Paul Paul COMP352 3
5555555 Lucie Trembaly COMP354 3

Table Student
StudentID StudentName Credits
1111111 William Smith 4
2222222 Michel Cyr 4
3333333 Charles Latan 4
4444444 Patricia Roubaix 4
2222222 Paul Paul 3
5555555 Lucie Trembaly 3

Table Course
Credits CourseName
4 COMP218
4 COMP353
4 COMP348
4 COMP353
3 COMP352
3 COMP354

Question: Is this decomposition lossless? Justify.

Exercise #3 (15 points)
Using the Functional Dependencies,
F = {A → BC ; CD → E ; B→D ; E→A}

a) Compute the closure of F (F+).
b) Is true / false : F ⊨  E → BC?
c) Provide the minimal cover Fc (min(F)) using steps shown in the class.
d) List of the candidate keys for R

Exercise #4 (15 points)
Consider the relation R(S, N, R, C, J, H, L) and the set of dependencies.
F = {S, N  C ; J, H, C  L ; J, H, L  S, N, R ; S, N, R  J, H, L}.
Prove that F ⊨ J, H, S, N  R using the Armstrong’s axioms?

Learn More

CIS515 Assignment 2: Database Systems and Database Models

The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server.

Imagine that you have been hired as a consultant to assist in streamlining the data processing of an international based organization that sells high-end electronics. The organization has various departments such as payroll, human resources, finance, marketing, sales, and operations. The sales department is the only department where employees are paid a commission in addition to their yearly salary and benefits. All other departments compensate their employees with a yearly salary and benefits only. Commission is paid by multiplying the employee’s commission rate by the total amount of product units sold. You have access to the following data sets:

Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID)
Invoice (InvNumber, InvDate, EmpNumber, InvAmount)
InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity)
Product (ProductNumber, ProductDescription, ProductCost)
Department (DepartmentID, DepartmentDescription)
Job (JobID, JobDescription)

Write a two to three (2-3) page paper in which you:

Design a query that will allow the finance department to determine the commissions paid to specific employees of the sales department for the month of December. Note: You will need to generate the tables described above (Employee, Invoice, InvoiceLine, Product, Department, and Job) in order to compare and validate your code. Validated query code must be part of your paper.
Compare the code of the query you designed in Question one (1) to one that would show how much total compensation is paid to each employee for the same month.
Determine and explain the factors necessary to ensure referential integrity.
Create an object-oriented model to show how the tables are interrelated through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Make sure that you are able to show the relationship types such as 1:M, 1:1, or M:1. Additionally, remember to include the determined factors from the previous assignment requirement. Note: The graphically depicted solution is not included in the required page length.
Identify which data components are the entities and attributes, and the relationship between each using an object representation diagram through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length.
Describe how Big Data could be used to assist in the productivity and forecasting of the organization’s products and resources.

Learn More

CS371 Database Design Week 5 Assignment Chapter 6 Questions

Chapter 6 Exercises 1 page 154: Use MySQL Workbench to construct the relational diagram for this database.
Chapter 6 Exercises 2 page 154: Use MySQL Workbench to construct the relational diagram for this database.
Chapter 6 Exercise 5 page 155: Submit responses in Word Doc format.

You may submit a single word doc with SQL queries and your models pasted in – OR – submit separate files including PDFs of your models and a word doc with your queries.

Chapter 6 Exercise 1 page 154: Use MySQL Workbench to construct the relational diagram for this database:

1.  Leslie’s Auto Sales has a relational database with which it maintains data on its salespersons, its customers, and the automobiles it  sells. Each of these three entity types has a unique attribute identifier.
The attributes that it stores are as follows:
• Salesperson Number (unique), Salesperson Name, Salesperson Telephone, Years with Company
• Customer Number (unique), Customer Name, Customer Address, Value of Last Purchase From Us
• Vehicle Identification Number (unique), Manufacturer, Model, Year, Sticker Price Leslie’s also wants to keep track of which salesperson sold
which car to which customer, including the date of the sale and the negotiated price. Construct a relational database for Leslie’s Auto Sales.

Answer:


Chapter 6 Exercise 2 page 154: Use MySQL Workbench to construct the relational diagram for this database.

2.  The State of New York certifies firefighters throughout the state and must keep track of all of them, as well as of the state’s fire departments. Each fire department has a unique department number, a name that also identifies its locale (city, county, etc.), the year it was established, and its main telephone number. Each certified firefighter has a unique firefighter number, a name, year of certification, home
telephone number, and a rank (firefighter, fire lieutenant, fire captain, etc.) The state wants to record the fire department for which each firefighter currently works and each firefighter’s supervisor. Supervisors are always higher-ranking certified firefighters.
Construct a relational database for New York’s fire departments and firefighters.

Answer:


Chapter 6 Exercise 5 page 155: Submit responses in Word Doc format.

5. In the General Hardware Corp. database of Figure 6.1, what would happen if:
a. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is restrict and an attempt is made to delete the record for customer 2198 in the CUSTOMER relation?
b. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is cascade and an attempt is made to delete the record for customer 2198 in the CUSTOMER relation?
c. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is set to null and an attempt is made to delete the record for customer 2198 in the CUSTOMER relation?
d. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is restrict and an attempt is made to delete the record for employee 33779 of customer 2198 in the CUSTOMER EMPLOYEE relation?
e. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is cascade and an attempt is made to delete the record for employee 33779 of customer 2198 in the CUSTOMER EMPLOYEE relation?
f. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is set-to-null and an attempt is made to delete the record for employee 33779 of customer 2198 in the CUSTOMER EMPLOYEE relation?

Answer:

Learn More

GU CS371 Database Design Week 6 Assignment Chapter 7

Fundamentals of Database Management Systems 2nd Edition
Chapter 7 Questions
Exercise 2 – Using MySQL Workbench, recreate the data model given to you but in a “well-structured” format. Note: This essentially means adding foreign keys. Be sure to choose key names that make sense.
Minicase 1 - Using MySQL Workbench, recreate the data model given to you but in a “well-structured” format. Note: This essentially means adding foreign keys. Be sure to choose key names that make sense.

Exercise:
2. Convert the Central Hospital entity-relationship diagram on the next page into a well-structured
relational database. (Instructions: Exercise 2 – Using MySQL Workbench, recreate the data model given to you but in a “well-structured” format. Note: This essentially means adding foreign keys. Be sure to choose key names that make sense.)

Minicase:
1. Happy Cruise Lines. Convert the Happy Cruise Lines entity-relationship diagram on the next page into a well structured relational database.
(Instructions: Minicase 1 - Using MySQL Workbench, recreate the data model given to you but in a “well-structured” format. Note: This essentially means adding foreign keys. Be sure to choose key names that make sense.)

Learn More

Murach’s SQL for SQL Server Chapter 10 How to create and maintain databases and tables with SQL statements
Exercises
1. Create a new database named Membership.
2. Write the CREATE TABLE statements needed to implement the following design in the Membership database. Include reference constraints. Define IndividualID and GroupID with the IDENTITY keyword. Decide which columns should allow null values, if any, and explain your decisions. Define the Dues column with a default of zero and a check constraint to allow only positive values.
3. Write the CREATE INDEX statements to create a clustered index on the GroupID column and a nonclustered index on the IndividualID column of the GroupMembership table.
4. Write an ALTER TABLE statement that adds a new column, DuesPaid, to the Individuals table. Use the bit data type, disallow null values, and assign a default Boolean value of False.
5. Write an ALTER TABLE statement that adds two new check constraints to the Invoices table of the AP database. The first should allow (1) PaymentDate to be null only if PaymentTotal is zero and (2) PaymentDate to be not null only if PaymentTotal is greater than zero. The second constraint should prevent the sum of PaymentTotal and CreditTotal from being greater than InvoiceTotal.
6. Delete the GroupMembership table from the Membership database. Then write a CREATE TABLE statement that recreates the table, this time with a unique constraint that prevents an individual from being a member in the same group twice.

Learn More