Search results for 'College'

South Community College (SCC) is structured like a typical community college. They have 3 semesters and a multitude of courses. Each course may have any number of sections in a given semester. For example, CTS2437 (SQL Server) may have one or more sections being taught in any given semester. SCC has 3 semesters (fall, spring, summer) which they refer to as A, B, and C. They refer to semesters by the year and the semester code. For example, fall 2011 would be referred to as 2011A. They need to keep track of students, courses, schedules, instructors, and grades earned in each course taken. They need a database to maintain these information. The student information would include the student name, address, phone#, and email. Students may have taken or are taking any number of courses. The grade earned for each course must also be maintained. The course information would include the course title and number of credits. Keep in mind that a given course may have many sections in any particular semester. SCC needs to maintain the instructor for each section in addition to the students and the grade they earned. The instructor information would include the instructor name, phone#, office#, and email address. South needs to maintain all courses that the instructor has taught or is currently teaching. Some of the requirements that SCC has requested in the database system include: • Student cannot register for the same section more than once. • A roaster of students can be produced for any given section. • GPA (Grade Point Average) can be generated for a student for any given semester, year, or entire school career. • GPA is calculated by adding up all of the grades earned (A=4, B=3, C=2, D=1, F=0) and dividing by the number of credits associated with the courses taken. • A transcript can be produced for a given student showing all courses taken and grades earned. Learn More

CS362 Structured Query Language for Data Management Week 2 IP

After creating the database schema, use Insert, Update, and Delete commands to populate the tables with the following information.

1. Insert the following classes' records:
Code     Name     Description
ACCT306     Accounting 1     This course introduces accounting concepts and explores the accounting environment. It covers the basic structure of accounting, how to maintain accounts, use account balances to prepare financial statements, and complete the accounting cycle. It also introduces the concept of internal control and how to account for assets.
CS362     Structured Query Language for Data Management     This course gives complete coverage of SQL, with an emphasis on storage, retrieval, and manipulation of data.
ENG115     English Composition     In this course, students focus on developing writing skills through practice and revision. Students will examine expository, critical, and persuasive essay techniques.
FIN322     Investments     This course focuses on investments and investment strategies. Various investment vehicles such as stocks, bonds, and commodities are examined. Students will explore the principles of security analysis and valuation.

2. Insert the following advisors' records:
Name     Email
Fred Stone     fred@college.edu
Bob Gordon     bob@college.edu
Jack Simpson     jack@college.edu

3. Insert the following students' records:
Name     Birthdate     Gender     StartDate     GPA     IsActive     AdvisorID
Craig Franklin     1970-03-15     Male     2010-05-30     3.10     Yes     3
Harriet Smith     1982-04-15     Female     2010-05-30     3.22     Yes     1
George David     1984-11-05     Male     2010-10-01     0.00     Yes     3
Ben Jefferson     1976-09-25     Male     2009-02-21     1.80     No, the student has gone on temporary leave to pursue other opportunities but plans on returning in 1 year.     3

4. Delete the course named Investments from the system.

5. Change Harriet Smith’s birthdate to April 25, 1982 and her GPA to 3.25.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Provide an introduction explaining the important of these commands in relation to your overall Key Assignment.

Learn More

COM 330 Chapter 6 Assignment

1. Using the STUDENT table structure shown in Table P6.4, do the following.

Table P6.4 Sample STUDENT Records
Attribute Name    Sample Value    Sample Value    Sample Value    Sample Value    Sample Value
STU_NUM    211343    200128    199876    199876    223456
STU_LNAME    Stephanos    Smith    Jones    Ortiz    McKulski
STU_MAJOR    Accounting    Accounting    Marketing    Marketing    Statistics
DEPT_CODE    ACCT    ACCT    MKTG    MKTG    MATH
DEPT_NAME    Accounting    Accounting    Marketing    Marketing    Mathematics
DEPT_PHONE    4356    4356    4378    4378    3420
COLLEGE_NAME    Business Admin    Business Admin    Business Admin    Business Admin    Arts & Sciences
ADVISOR_LNAME    Grastrand    Grastrand    Gentry    Tillery    Chen
ADVISOR_OFFICE    T201    T201    T228    T356    J331
ADVISOR_BLDG    Torre Building    Torre Building    Torre Building    Torre Building    Jones Building
ADVISOR_PHONE    2115    2115    2123    2159    3209
STU_GPA    3.87    2.78    2.31    3.45    3.58
STU_HOURS    75    45    117    113    87
STU_CLASS    Junior    Sophomore    Senior    Senior    Junior

c. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.

d. Draw the Crow’s Foot ERD.

Load the data from table P6.4 into an Access database. Add 5 additional students (make up your data) following the table structure.
Create a query in Access that list the students who have a Statistics major.
List the students who have a GPA of 3.45 or higher in a report.
Report the students who have the same advisor.

Submit c and d as an attachment, attach your Access database.

Learn More

Create a database using MS ACCESS or SQL (your choice).  Your database will be a college database, such as Tiny College that tracks students, the classes they are enrolled in, the location of the class and the instructor of the class.  You will also show reports and queries to your database.  Such as all students who have made an A in History 101, or all the classes that Professor Smith teaches and what buildings/room that he teaches in.  How many students are Math majors?  Etc….. These are just some examples; use your knowledge of system life cycle, prototyping, Implementation support, organizational issues, standards, etc…

Minimum of 25 students, 10 professors and 10 buildings/rooms, and 15 classes.

Learn More

Microsoft Access 2010 Chapter 7 Make It Right Correcting Errors in the Query Design

Analyze a database, correct all errors, and/or improve the design.

Instructions: Start Access. Open the College Pet Sitters database. See the inside back cover of this book for instructions for downloading the Data Files for Students, or see your instructor for information on accessing the files required in this book.

College Pet Sitters is a database maintained by a small pet-sitting business owned by college students. The queries shown in Figure 7 – 37 contain a number of errors that need to be corrected before the queries run properly. The query shown in Figure 7 – 37a displays the Enter Parameter Value dialog box, but this is not a parameter query. Also, the owners wanted to assign the name, Total Amount, to the Balance + Paid calculation. Save the query with your changes.

When you view the results for the query shown in Figure 7 – 37b, you get 30 records. You know this is wrong. Also, the query did not sort correctly. The query results should be sorted first by sitter number and then by descending balance. Correct the errors and save the query with your changes.

Change the database properties, as specified by your instructor. Submit the revised database in the format specified by your instructor.

Learn More

Student Database Oracle DDL and Queries

Create the following tables.
STUDENT
Student Number (PK)
Student Last Name
Student Major
Department ID (FK)
Student GPA
Student Hours
Student Class
Advisor ID (FK)

ADVISOR
Advisor ID (PK)
Advisor Last Name
Advisor Office
Advisor Building
Advisor Phone

DEPARTMENT
Department ID (PK)
Department Code
Department Name
Department Phone

*NOTE* You will have to decide on how to handle the Department and Advisor ID foreign keys in terms of a numbering system as well as appropriate field widths and types for the fields.
The business rules which govern this database are: A student may have one advisor while an advisor may advise multiple students. A student belongs to only one department but each department can have many students.
Populate the table with data from Table P6.4 (p. 217).

There is an error in the book! Change Ortiz’s student number to be 200888. In addition, add these three students to your database.
STU_NUM 123984 995133 367181
STU_LNAME Freeman Wilder Green
STU_MAJOR CIT CIT BIS
DEPT_CODE CS CS IS
DEPT_NAME Computer Science Computer Science Business Informatics
DEPT_PHONE 5234 3951 3951
COLLEGE_NAME Informatics Informatics Informatics
ADVISOR_LNAME Strand Zhang Goh
ADVISOR_BLDG Griffin Griffin Griffin
ADVISOR_OFFICE 5132 3451 5612
ADVISOR_PHONE 1603 3512 7922
STU_GPA 2.5 3.9 2.3
STU_HOURS 97 58 63
STU_CLASS Senior Junior Junior

Part 1
Provide all DDL-related code. This includes table definition and creation, fk/pk creation, and populating the tables with data. Please include the output from Oracle that shows that everything was created correctly.

Part 2
Queries – For each query provide 1) What the output, specifically, should be based upon eyeballing the data 2) the SQL code used to generate the query, and 3) the output from Oracle. Please include any relevant fields you think the user of the query would need to interpret the output.
1. Advisors need the capability to generate a query that returns *all* student information based upon a student number. This will help them in the advisement process. Create a query that returns all information for student Freeman from each table.
2. Each year, college administrators need to know how many students are in each major. Create a query that counts the number of students in each major while displaying the major name.
3. Kroger has approached NKU with an internship opportunity! The business department chair needs to generate a mailing list to inform great students about a job opportunity. Create a query that shows all the student numbers, last names, majors, department names, and advisor’s last name for students in Business Admin. Students should have a minimum GPA of 2.5 or greater to be on this mailing list. List in ascending order of GPA.
4. To get an idea of the adequacy of admission standards, NKU needs to have an idea of the breakdown of students. By each college, show the number of students in each classification (freshmen, sophomore, junior, senior).
5. Due to a fire, Griffin Hall has burnt down. Write a SQL statement that updates all faculty who had offices in Griffin Hall to now be housed in the University Center.
6. New funding may be able to pay for an advising center. The dean would like to get an idea of how many students each faculty member currently advises. Create a query that shows the names and majors of students for each advisor along with the advisor’s name.
7. (Extra credit) Create a query that counts the number of students who are eligible for the Dean’s list (GPA >= 3.5) in each department.

Learn More