DATABASE AND CLIENT/SERVER APPLICATIONS Mid-Semester Exam

Name: avalos demetrio maria guadalupe
INSTRUCTIONS
1. Use the word processor on the computer to fill in this assessment. 2. Start by inserting your name in the space above. 3. Before you begin answering these questions, save this file using the required file name format: “<YourStudentID> Mid_Semester_Exam.doc” where <YourStudentID> is your StudentID and there is a blank between it and the Mid_Semester_Exam. When you are finished with the assessment, save the document again and then upload it to the LMS. 4. Click the Choose File button. 5. Locate the file for attachment. 6. Once the file is attached, click Upload File.
ASSESSMENT QUESTIONS 1. Design a customer database. It should support listing:
• customers with their name, a customer identifier, a billing address (assume U.S. addresses), a shipping address that may be different, a contact with associated phone number and email address.
• orders including an order number, customer id, order date, product identifiers with quantity and price at order time.
• products with a name, identifier, description, current price, and weight (we will not worry about other size measures that might be useful in shipping).
• inventory of products in stock including where in the warehouse it is (Aisle, Shelf, Bin), and quantity there.
Draw an entity relationship diagram in third normal form for this database. You may use either the “normal” ER notation (boxes and diamonds) or the “simplified” ER notation.The ER diagram is worth 18 points.
You may make the following assumptions: a) All customers have made orders. b) Products may or may not have been sold to customers. c) All products are stored in one location in the warehouse. d) There is always inventory in stock.

2. Discuss the domains for the following attributes in the customer database. (There is no specific “correct” answer for what the domains should be. That depends on the problem, needs of the customer, and choices of the designer.)Worth 2 points each. a) CUSTOMERS.CustomerName Es importante ya que el cliente obligatoriamente debe tener un nombre, el dominio de este atributo es tipo VARCHAR, la longitud de la cadena de caracteres sera de 50 b) CUSTOMERS.ContactName Es importante ya que el cliente obligatoriamente debe tener un numero de contacto, el dominio de este atributo es tipo NUMBER, la longitud de números permitidos será de 15 c) ORDERS.OrderDate Es importante ya que una orden obligatoriamente debe tener una fecha de realización, el dominio de este atributo es de tipo DATE ya que pide la fecha de orden, este será dividido en año, mes y dia. d) ORDERDETAILS.OrderPrice Es importante ya que una orden obligatoriamente debe tener un precio, el dominio de este atributo es tipo DOUBLE ya que solo pide el precio de orden, es de este tipo ya que el precio pude variar entre entero o con punto decimal. e) PRODUCTS.ProductDesc No es necerario ya que el producto puede o no puede tener una descripción, el domio de este atributo sera de tipo VARCHAR ya que pide agregar descripción sobre este producto, la longitud de la cade de caracteres será de 100. Using the following ER diagram for a student information system (SIS) database, and making any necessary and reasonable assumptions about the domains of the attributes, answer questions 3 through 8 below.Worth 2 points each.

3. Name a junction table (relationship relation) in the SIS design.
La table Students tiene relacion con StuClasses ya que un estudiante forma parte de una clase.

4. Why is a junction table (relationship relation) needed in the design?
Es una tabla de union ya que una relacion R se incluye en los atributos de la clave primaria de S, la clave primaria de R será una de las claver foráneas, y se requiere una operación de unión adicional al combinar tuplas de las tablas relacionadas.

5. What is the primary key of the STUCLASSES table?
ClassID

6. What kind of key is the primary key of the STUCLASSES table?
Llave primaria compuesta porque necesita la clave primaria de Student y Course.

7. What kind of key is CourseID in the CLASSES table?
Llave primaria compuesta porque necesita la clave primaria de Student y Course.

8. What is a (reasonable) domain for Credits in the COURSES table?
El dominio para el atributo Credits es obligatorio y de tipo NUMBER.

Write the SQL commands that will implement each of the following scenarios in questions 9 through 12 below. Use multiple SQL commands if necessary; clearly label any views used.Worth 15 points each.

9. List the students registered for at least one course in Spring 2011. Include student name, id, and email address. Order by last name, first name, and middle name.
Select LastName, FirstName, MiddleName, StudentID, Email
From StuClasses, Classes
Where StuClases.StudentID=Classes.CourseID
Order By LastName, FirstName, MiddleName;

10. List the classes taken by John Smith, student id 123456, in Spring 2010. Include student name, id, course id and name, term, year, credits, and grade for the course. Order by course id.
Select LastName, FirstName, MiddleName, CourseID, CourseName, Term, Year, Credits, Grade
From StuClasses, Classes
Where StudentID=123456 and Year=2010
Order By CourseID;

11. List the grade point average for each student at the university. You may assume that all classes should be included (no “re-takes” of classes) and that Grade is a number ranging from 0 to 4.0. Include student name, id, and GPA.
Select LastName, FirstName, MiddleName, StudentID, Grade
From StuClasses
Where Grade between 0 and 4;

12. List the courses that have never been offered. Include course id and name, order by course id.
Select CourseID, CourseName
From Courses
Where Credits is NULL;