Ciara Hendricks D205 PA

create an ER table

Database Systems: Design and Application

When translating an ER model into database tables   a.) all of the entity attributes become the foreign keys. b.) the entity attributes become the tables. c.) for each entity, two tables need to be created. d.) the entity attributes become the fields of the new table.    The goal of normalization is to, (please select all that apply): a.) eliminate data duplication. b.) is create only 1:N relationships. c.) to create as few as possible tables to promote efficiency between the tables. d.) construct tables so that each table has a single topic or theme.

Database normalization is needed. 1NF, 2NF, 3NF, BCNF and 4NF. If some steps can be skipped, please expain why. ex) BCNF is not needed because .... Each relation (table) represents a single subject. For example, a COURSE table will contain only data that directly pertain to courses. Each row/column intersection contains only one value and not a group of values. No data item will be unnecessarily stored in more than one table (tables have minimum controlled redundancy). All nonprime attributes in a relation (table) are dependent on the primary key - the entire primary key and nothing but the primary key. Each relation (table) has no insertion, update, or deletion anomalies, which ensures the integrity and consistency of the data.

Assume that you are a database specialist for Kulliyyah of Information and Communication Technology (KICT). You are required to create a conceptual data model for Abu Hurairah Club. The following are the data requirements gathered: The Abu Hurairah Club has a few animal shelters. Each shelter is identified by an identification number and has an address, a phone number, and a set of days on which it is open. A shelter is managed by one employee. An employee has an identification number, name, address, phone number, and a pay rate. A volunteer has an identification number, name, address, phone number, and a meal allowance. A person, who works at a shelter, is either an employee or a volunteer, but not both. A person may work at more than one shelter for some regular set of days each week. An animal is housed at one shelter. Each animal has an identification number that is unique to its shelter, a name, a birth date, and a species.

Assume that you are a database specialist for Kulliyyah of Information and Communication Technology (KICT). You are required to create a conceptual data model for Abu Hurairah Club. The following are the data requirements gathered: The Abu Hurairah Club has a few animal shelters. Each shelter is identified by an identification number and has an address, a phone number, and a set of days on which it is open. A shelter is managed by one employee. An employee has an identification number, name, address, phone number, and a pay rate. A volunteer has an identification number, name, address, phone number, and a meal allowance. A person, who works at a shelter, is either an employee or a volunteer, but not both. A person may work at more than one shelter for some regular set of days each week. An animal is housed at one shelter. Each animal has an identification number that is unique to its shelter, a name, a birth date, and a species. Based on the requirements from a, b, c, d and e, draw…

1NF to 4NF Data Normalization is needed. Each relation (table) represents a single subject. For example, a COURSE table will contain only data that directly pertain to courses. Each row/column intersection contains only one value and not a group of values. No data item will be unnecessarily stored in more than one table (tables have minimum controlled redundancy). All nonprime attributes in a relation (table) are dependent on the primary key - the entire primary key and nothing but the primary key. Each relation (table) has no insertion, update, or deletion anomalies, which ensures the integrity and consistency of the data.

Databases and Database Users exercise: Figure 1.2 is on image Consider Figure 1.2. a.) If the name of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (Computer Science and Software Engineering) Department and the corresponding prefix for the course number also changes, identify the columns in the database that would need to be updated. b.) Can you restructure the columns in the COURSE, SECTION, and PREREQUISITE tables so that only one column will need to be updated? Figure 1.2 is on image

“Martial Arts R Us” (MARU) needs a database. MARU is a martial arts school with hundreds of students. The database must keep track of all the classes that are offered, who is assigned to teach each class, and which students attend each class. Also, it is important to track the progress of each student as they advance. 1.Create a complete Crow’s Foot ERD for these requirements: 2.Create a relational diagram for the data model. 3.Draw the dependency diagram showing partial and transitive dependency(if any). 4. Normalize the tables to 3NF. 5. Show the dependency diagram after normalization. 6. Use SQL to create all tables in Access. Do not forget FK,PK 7. Create relationships.

Subject: data base management systems

Topic: Database Design - Entity Relationship Modeling (Please answer the below question in 250 words or more). Thank you! It is known that we should avoid redundant data. Let’s say you have a Product Table with the cost of an item, and an Order table that has information about orders from customers. What would be the benefits to also storing the item's cost in the Order table?

Create an ER model related to the following entities and relationships: Department, Student, Professor, Course, Section, Grade, Major. (For attributes inside each table, you only need to insert several reasonable attributes. No need to have a comprehensive list. For many-to-many relationship, you also need to insert one extra table to handle the many-to-many relationship. The final database design will have more tables than the number of entities listed above.) A given department can offer many courses. A given course can be offered by only one related department. A given course can have many related sections. A given professor can teach many sections. A given section can only be related to one course and taught by one professor. A given professor can have appointment with different departments A given department can have many professors. A given department can offer several majors. A given major can be offered by only one related department. A given student can have several majors. A…

DataBase case study: read this case study and finilize what should be the enitties that is mentioned in the case study and then make a ERD daigram of this data.   Delhi Mart is a super Store located at Mahavir Enclave, New Delhi, India.  Delhi Mart has many types of products that are related to specific Category.  Delhi Mart are avail many different customers to sell their products daily. Every Category is managed by a particular manager. Customer order their selected product in terms of qty and avail selected discount on the product.  Delhi Mart also have staff that manage different departments in the mall. Every Customer is concern with a particular staff to resolve their queries. Every Manager is responsible to maintain the stock if the stock will out they contact their selected supplier for buying products.

Q4: Design a database for an airline. The database must keep track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routing of future flights. Your design should include a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints. Please note that an E-R diagram is not required.

Consider the unnormalized data presented in the STUDENT table below:a) Identify the data redundancies and give reasons why they create problems in schemas. STUNUM STUNAME TUTNUM TUTNAME COURSENUM COURSEDESC GRADE 00118536 John Brown  RC985 Rose Carter CS1001 CS1002 CS1003 Comp Research English Database design B A A 00118537 Marie Smith TB526 Tom Black CS9001 CS9002 CS1002 Calculus Javascript English A B C 00118538 Mary Cole PF587 Peter Finch  CS9002 CS1003 CS1001 Javascript Database design Comp Research C A A 00118539 Ian Dunkley SH231 Sarah Hardy CS9005 CS9006 CS1002 Information Systems Web Design English A B B 00118540 Bob Marley JH850 Jacqui Hart CS9002 CS1002 CS1003 JavaScript English Database Deign  A B C b) Which dependencies represent 1NF violations? Identify and eliminate 1NF violations. Present your table(s) after transformation into 1NF.c) Identify and remove any 2NF violations. Show the resulting schemas and tablesd) Identify and…

Create an Entity-Relationship Model for the Queen Anne Curiosity Shop The first assignment for the class project is to create an entity-relationship model of the Queen Anne Curiosity Shop database. This assignment requires you to apply the knowledge gained from study of chapter 4 of the textbook. You will create the entity-relationship model using the ERDPlus database modeling tool. The Queen Anne Curiosity Shop database contains the following tables: CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, EmailAddress) EMPLOYEE (EmployeeID, LastName, FirstName, Phone, EmailAddress) VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, EmailAddress) ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID) SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total) SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice) Note that the above table descriptions follow the textbook convention:…

Problem A: A retail chain of pharmacies, Prescriptions-R-X that sells prescription drugs has hired your team of data analyst to design its database. Here's the information that your team gathers after discussions with managers from Prescriptions-R-X: Patients are identified by an SSN, and their names, addresses, and ages must be recorded. Doctors are identified by an SSN. For each doctor, the name, specialty, and years of experience must be recorded. Each pharmaceutical company is identified by name and has a phone number. For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company, and the trade name identifies a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer. Each pharmacy has a name, address, and phone number. Every patient has a primary physician. Every doctor has at least one patient. Each pharmacy sells several drugs and has a…

A Professor wants a database for his new University that will maintain data for the following entities with their attributes: a. Courses: title, credits, syllabus and prerequisites b. Course offered: course ID, year, semester, section number, instructor(s), timings and classroom. c. Students: student_ID, name and program d. Instructors: Instructors_ID, name, department and title They require a proper model to help with the enrolment of students into courses and rightfully graded after the course is rolled out. Construct an E-R diagram for the professor's University. Indicate all Primary keys, relationships and constraints. Document all assumptions that you make about the mapping constraints.

Consider the data about students in your university's database as a huge table containing a row for each student and a column for each data element. Sort the data into main categories such as contact information, medical information, financial information, and academic information. Which employment categories should be accessible to which groups of people? Who should have access to a single row within a group, who should have access to multiple rows but not to all rows, and who should have access to all rows in the table? Should anyone have the ability to view data but not alter it?

- The restaurant has two waiters (named John and Ali) taking order and collect payment from the customer. The customer can choose to pay using cash, Touch’n Go or credit card. The customer detail in the receipt is optional. Base on the given information above, draw a complete Entity-Relationship Diagram (ERD) with Crow’s Foot notation. Write all appropriate attributes, connectivity and cardinalities in the ERD.

You have been asked to design and implement a database for CTU, which includes 3 entities (tables), as follows: Students Faculty Courses Part 1 Create a Word document that includes the following: Identify at least 3 attributes for each entity, including the following: Student's entity should have an attribute for the program the student is enrolled in. Faculty should have an attribute identifying their rank. Properly identify all of the primary keys (use solid underlines). Create an entity relationship diagram (ERD) from the entities. You may use SmartDraw, Visio, Erwin, or even Word to draw the diagram. Take a screenshot (no need if you use Word), and insert it into the Word document. Part 2 Use Microsoft Access to implement the database that you designed in Part 1, and include the following: Create the Student, Faculty, and Course tables in Access. Add a few instances in each table. Create 2 queries for the following objectives: Find the student names for all students who are in the…

7. In the context of database design, consider the following scenario: There is database table that contains columns for employee ID, employee name, department, department location, and project details. Upon analyzing the table, you notice that there are multiple rows with the same employee name, department, and department location, but different project details. Based on this scenario, which of the following best describes the normalization process that should be applied to improve the database design? A) Split the table into two: one containing employee ID and employee name, and the other containing department, department location, and project details. B) Combine all columns into a single table to reduce redundancy. C) Normalize the database to the third normal form by creating separate tables for employee details, department details, and project details, and establish relationships between them. D) No action is required as the table already follows the normalization principles.

OWN WORK. COMPLETE DIAGRAM FOR UPVOTE

Draw ONLY an EER model for the data requirements described in question 3.3E of the textbook. Pleasecomment on the limitations of your EER model. Please clearly state any assumption

Exercise 1-2 A small library keeps a roster of who will be at the desk each day. They have a database table as shown in Figure 1-8. week_start - Mon - Tue - wed - Thur - Fri 17/10/2011 Jane 24/10/2011 Jane Sue George Sue Jane Sue Linda Sue Lee 31/10/2011 Sue Sue Lee George George Figure 1-8. An initial database table to record roster duties What problems can you foresee in making good use of this information? Suggest some better ways that this information could be stored.