Accounting Information SystemsDatabases
Accounting Information SystemsDatabases MIS 3353
Popular in Course
Jesse West DDS
verified elite notetaker
verified elite notetaker
Minerva Schinner PhD
verified elite notetaker
Minerva Schinner PhD
verified elite notetaker
Minerva Schinner PhD
verified elite notetaker
verified elite notetaker
Popular in Management Information Systems
This 40 page Class Notes was uploaded by Jesse West DDS on Monday October 26, 2015. The Class Notes belongs to MIS 3353 at University of Oklahoma taught by Staff in Fall. Since its upload, it has received 7 views. For similar materials see /class/229307/mis-3353-university-of-oklahoma in Management Information Systems at University of Oklahoma.
Reviews for Accounting Information SystemsDatabases
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 10/26/15
MIS 33535113 Workbook Fall 2001 Database Table of Contents 1 Enterprise Modeling II Entities and relationships HI r 39 Mndelino Working from the bottom up 39 Unary 39 quot l EER models Business rules VI SQL Simple SQL The SHARE table More complex SQL the DONOR tables Multiple table queries The SALES tables Querying a Unary 1 VII Physical Desi on 26 26 29 1 Enterprise Modeling The NewAge Tax Preparation Company is a franchise operation They sell one or more franchises to owners who agree to use NewAge training programs and abide by the company s policies The company s primary sources of revenue are franchise fees and products ordered by franchisees The company operates as follows owners enter into franchise agreements as a result they own at least one franchise location Within each location there are employees who provide a taX service to customers In addition franchise locations order products from the company headquarters for example forms brochures etc Draw the enterprise model 2 The UR moving company s main source of revenue is that which is generated from its drivers and trucks The company is interested in keeping track of how many hours each driver drives each truck A driver may drive one or more trucks and a truck may be driven by one or more drivers Because VT is a small venture there are only three types of trucks in their eet These are 15foot 25foot and 30foot Each truck has a single maintenance worker permanently assigned to perform maintenance on the truck although a maintenance worker may maintain more than one truck II 4 V39 0 Entities and relationships A student takes several courses each course has many students An instructor teaches several courses but each course is taught by one instructor Course has several sections each section pertains to one course Course may have several textbooks a given textbook is used in only one course III Conceptual Modeling 7 Create a conceptual model for the following rm A rm has a number of sales of ces Attributes include of ce number and location Each sales of ce is assigned one or more employee Attributes include employee ID and name An employee must be assigned to only one sales of ce For each sales of ce there is always one employee assigned to manage that of ce An employee may manage only the of ce to which heshe is assigned The rm lists property for sale Attributes include property ID and location Components of location include address city state and zip Each unit of property must be listed with only one sales of ce An of ce may have any number of properties listed or no properties Each unit of property has one or more owner Each owns some percentage of the property Attributes include name and ID A person can own more than one property 9 gt0 The Marathoner a monthly magazine regularly reports the performance of professional marathon runners It has asked you to design a database to record the details of all major marathons eg Boston London and Paris Professional marathon runners compete in several races each year A race may have thousands of runners but only about 200 or so are professional competitors the ones that The Marathoner tracks For each race the magazine records a runner39s time and finishing position some personal details like name gender and age and race details like conditions number of competitors and date A laboratory has several chemists who work on one or more projects Chemists also may use certain kinds of equipment on each project Chemist information includes employee ID name phone number Project information includes ID and start date Information maintained about equipment includes serial number and cost The organization wishes to record date assigned when equipment is assigned to a chemist working on a project A chemist must be assigned to at least one project and one equipment item A given piece of equipment need not be assigned and a given project need not be assigned either a chemist or equipment 10 A hospital has a large number of registered physicians Each physician has an ID and specialty Patients are admitted to the hospital by physicians Patient information includes ID and name Any patient who is admitted must have exactly one admitting physician A physician may optionally admit any number of patients Once admitted a given patient must be treated by at least one physician A particular physician may treat any number of patients or may not treat any Whenever a patient is treated by a physician the hospital wishes to record the details of the treatment such as date time and results 11 Steve operates a cinema chain and has given you the following information I have many cinemas Each cinema can have many theaters Movies are shown throughout the day starting at 11 am and nishing at 1 am Each movie is given a 2hour time slot We never show a movie in more than one theater at a time but we do shifts movies among theaters because seating capacity varies I am interested in knowing how many people classi ed by adults and children attend each showing of a movie I vary ticket prices by movie and time slot 12 At SalesRUs invoices are written by sales reps Each sales representative can write many invoices but each invoice is written by a single sales representative An invoice is written for a single customer however each customer can have many invoices An invoice can include many detail lines which describe the products bought by the customer Products are supplied by different vendors 13 A database for a local garage is needed The database contains data items for a customer account number the customer s name the customer s address the customer s work telephone number the customer s home telephone number date of work done automobile make automobile model description of work done parts needed to complete the work charge for parts needed and charge for labor performed For warranty reasons data must be maintained in the database for at least ninety days therefore a customer may have several records in the database at any particular time Identical parts have only one cost but different parts have different costs for example all tires cost the same and all engines cost the same but a tire and an engine do not cost the same A customer may have more than one car Draw the ER diagram to model these data relationships Working with less information 14 Consider a simple course enrollment database for use by the Division of Management in the CBA The department offers several courses At the beginning of the semester the Division Director assigns an instructor to each course and publishes a directory listing course and instructor of ce By the 3rd week the department produces a course roll for its own records At the end of the semester the instructor posts grades for each student in the course for that semester 15 Mary Richards owns a house painting company She has decided that she needs a better recordkeeping system that can be used to quickly retrieve information about her current and former customers such as their name and phone number She already has detailed data about the individual jobs that her company has done for these customers such as the job number the beginning and end dates of the job a brief description of the job and the amount billed However Mary would like to be able to easily relate this data to the job s customer and to her employees who worked on the job Each job has a foreman and several other painters on it She also has information about all of her employees their SSN their name their salary and their phone number Sometimes an employee will be a painter on one job and a foreman on another When a customer calls with a complaint about the work being done on their house Mary needs to be able to determine who is or was the foreman on the job and who else worked on the job Often times customers are referred by other customers Mary thinks it would be a good idea to store data about the referral sources When a customer refers another customer Mary sends 10 to the referring customer with a thank you note Mary doesn t recognize multiple referral sources for an individual customer and she has no interest in storing data on the selfreferrals Of course a customer may refer many other customers to Mary 16 Each semester each student must be assigned an adVisor who counsels students about degree requirements and helps students register for class Each student must register for class with the help of an adVisor but if the assigned adVisor is not available the student may register with any adVisor We must keep track of the students assigned adVisors and name of adVisor with whom the student registered in the most recent semester Timestamping 17 The entity type STUDENT has the following attributes StudentiName Address Phone Age Activity and NoiofiYears Activity represents some campusbased student activity while NoiofiYears represents the number of years the students has participated A given student may engage in more than one activity Working from the bottom up 18 Convert the following to a conceptual model Unary Relationships 19 Assume that at Pine Valley Furniture each product described by product number description and cost is comprised of at least three components described by component number description and unit of measure and components are used to make one or many products In addition assume components are used to make other components and that raw materials are also considered components EER models Extended ER models can have supertypesubtype relationships and business rules 20 A bank has three types of accounts checking savings and loan The attributes for each account are as follows CHECKING AccountiNo Date70pened Balance ServiceiCharge SAVINGS AccountiNo Date70pened Balance InterestiRate LOAN AccountiNo Date70pened Balance InterestiRate Payment Assume that each bank account must be a member of at least one of these subtypes 21 A nonpro t organization depends on a number of different types of persons for its successful operations The organizations maintains the following information for these persons SSN name address and phone Three types of persons are of greatest interest employees volunteers and donors For employees the organization maintains a date hired and for volunteers the organization maintains a list of skills Donors donate items They can donate one or more items and an item may be donated by more than one donor ie a joint gift Persons may belong to more than one of these groups or to none of them 22 The American kennel association is trying to create a database of AKC registered dogs prior to their sale They are preparing a prototype in Indiana For each puppy they want to retain the puppy s number AKC registration code and the puppy s official name They also need to know if the puppy is show quality or pet quality If show quality then they want to retain the date of last show and the place the puppy finished If it is pet quality they want to know the defect and if the puppy likes children Each puppy lives in a kennel Each kennel has a code a name and a location Most kennels house quite a few puppies The kennel association also wants to keep track of the tricks each dog can do A trick can have an id code and a name Some puppies can t do any tricks while others can do many tricks It s also important to know when a puppy learned a certain trick 23 A local Karate shop offers group and private lessons Students who have names addresses phone numbers can take either kind of lesson or both Group lessons are offered for beginner advanced and intermediate levels Private lessons are unique since each one has only one student and its content depends on the student It s also important for the Karate shop to know how long a student has been a student Business rules 24 Consider the following rule An employee may only be assigned to jobs for which he she has been certi ed a draw the BER diagram segment b identify the constrained object and constraining object 25 One university gymnastics team is interested in maintaining information about its current schedule and meet performance The team is made up of 10 gymnasts Gymnast information includes name major and hometown The team competes in meets many times during the season Meet information includes date location and score Meets consist of four events vault beam oor and uneven bars The final team score is an aggregation of event scores and event scores are aggregated individual gymnast scores A meet can have one or more opponents The team faces each opponent only once during the season A gymnast can compete in all or some of the events though the team is limited to 5 participants in each event Gymnasts can only compete in events for which they are trained IV Relational Schema 26 Draw the relational schema for the following ER diagram COURSE ISJrereq 20 27 Draw the relational schema for the following ER diagram Se ction Hasi scheduled SECTION Semester Q COURSE 28 Draw the relational schema for the following ER diagram Project iName 22 29 Draw the relational schema for the following ER diagram CONCERT SEASON c onductorilD ConductoriName C ompositionilD A Name 4 COMPOSITION SOLOIST Soloist Name M ovementi Number Name 23 V N 0 rm aliz ation The process I highly recommend you follow when completing a normalization problem is as follows 1 draw the data model 2 convert it to relational schema 3 note the functional dependencies 4 normalize Any problems at the end of Chapters 3 or 4 would also provide good practice at this 4step process 30 Course and Section Problems Normalize the following a Course DEPT DEPARTMENT COURSE CRSTIME FACULTY FAC NAME b Depa1tment DEPT PHONE FACULTY FAC NAME MAJOR MAJOR TITLE c Section DEPT DEPARTMENT INSTR INSTNAME COURSE TIME d CourseiOutcome COURSE CRSENAME STUDENT STUNAME GRADE e Section COURSE COURSENAME INSTR INSTRNAME STUDENT STUNAME f Create a database from a through e that is in 3NF 31 Soccer Team Problems a Player PLAYERID NAME TEAM COACH b Player PLAYERID PLAYERNAME PLAYERADDRESS TEAM COLOR COACIUASST COACHPHONE c Create a database from a through b that is in 3NF 24 32 INVOICE Customer Number Order Number Product Number Customer Name Address Order Date Order Quantity Product Description Unit Price 0 Customers can place multiple orders and each order can be for a single or multiple products 0 Customers can order single or multiple quantities for each product 33 Normalize the following relation Owner Agent Property Price AgentPhone llL l 25 VI SQL The Access databases for these practice sets are available at httpfacultystaifoueduCTraciACaIte1mis3353databases Simple SQL The SHARE table The data HARE SHRCODE SHRFIRM SHRPRICE SHR TY SHRDIV AR Abyssinian Ruby 3182 22010 13 BE Burmese Elephant 007 154713 001 3 BS Bolivian Sheep 1275 231678 178 11 CS Canadian Sugar 5278 4716 25 15 FC Freedonia Copper 275 10529 184 16 ILZ Indian Lead amp Zinc 3775 6390 3 12 NG Nigerian Geese 35 12323 168 10 PT Patagonian Tea 5525 12635 25 10 ROF Royal Ostrich 3375 1234923 3 6 SLG Sri Lankan Gold 5037 32868 268 16 1 Draw a normalized ER model for this data 2 Write the SQL statements necessary to create this database 26 Write S QL to solve the following problems 3 List a share39s name and its code 4 List full details for all shares with a price less than one dollar 5 List the name and price of all shares with a price of at least 10 6 List the name share price share holding and total value of shares held this is number of shares times share price 7 List the name of all shares with a yield exceeding 5 percent yield is equal to the diVidend diVided by the price 8 Report the total diVidend payment of Patagonian Tea the total diVidend payment is the diVidend times quantity 50 Find all shares where the price is less than 20 times the diVidend 10 Find the share with the minimum yield 11 Find the total value of all shares with a PE ratio gt10 27 12 Find the share with the maximum total dividend payment 13 Find the value of the holdings in Abyssinian Ruby and Sri Lankan Gold 14 Find the yield of all rms except Bolivian Tea and Canadian Sugar 15 Find the total value of the portfolio 16 List rm name and value in descending order of value 17 List shares with a rm name containing Gold 18 Find shares with a code starting with 39B 28 More complex SQL the DONOR tables DONOR DONORNO DLNAME DFN AME DPHONE DSTATE DCITY 101 Abrams Louis 5559018 GA London 102 Aldinger Dmitry 5551521 GA Paris 103 Beckman Gulsen 55 5 8247 WA Sao Paulo 104 Berdahl Samuel 55 5 8149 WI Sydney 105 Bomeman Joanna 5551888 MD Bombay 106 Brock Scott 55 52142 AL London 107 Buyert Aylin 5559355 AK New York 108 Cetinsoy Girwan 55 563 46 AZ Rome 109 Chisholm John 5554482 MA Oslo 110 Crowder Anthony 5556513 NC Stockholm 111 Dishman Michelle 5553903 NC Helsinki 112 Duke Peter 55 54939 FL Tokyo 113 Evans Ann 5554336 GA Singapore 114 Frawley Todd 55 54785 MN Perth 115 Guo John 5556247 MN Moscow 116 Hammann John 5555369 ND Kabaul 117 Hays Cami 5551352 SD Lima 118 Herskowitz Thomas 5556872 MT London 119 Jefts Robert 5558103 ME Oslo YEAR 1992 5000 1993 5000 1994 5500 1995 5000 GIFT AMOUNT YEAR IDONORNO 1992 101 543 1992 102 1185 1992 103 838 1992 109 582 1992 110 887 1992 111 666 1992 112 223 1992 114 82 1992 117 186 1992 119 939 1993 101 899 1993 102 1362 1993 103 667 1993 105 674 1993 108 297 1993 110 332 1993 111 558 1993 115 268 1993 116 772 1993 119 111 1994 102 5208 1994 103 332 1994 107 155 1994 108 499 1994 109 84 1994 110 882 1994 111 560 1994 113 835 1994 114 345 1994 116 15 15 1m 60 1995 106 823 1995 110 812 1995 112 265 1995 116 657 1995 117 17 1995 118 30 3 Draw a normalized ER model for this data 4 Write the SQL statements necessary to create this database 31 Write S QL to solve the following problems 1 List the phone number of donor 112 2 How many donors are there in the donor table 3 How many people made donations in 1992 4 What is the name of the person who made the largest donation in 1992 5 What was the total amount donated in 1993 6 List the donors who have made donations in every year 7 List the donors who give twice the average 32 8 List the total amount given by each person across all years sort by donor name 9 Report the total donations in 1994 by state 10 In which years did the total donated exceed the goal for the year 33 Multiple table queries The SALES tables 1 Create a relational schema for this diagram 34 Perform th e following SQL queries using your relational schema 2 List the green items of type C 3 Find the name of green items sold by the recreation dept 4 Find the items not delivered to the books department 5 Find the departments that have never sold a Geo positioning system 6 Find the departments that sell at least four items 7 Find the items not delivered by Nepalese Trading Company 9 Find the items sold by at least 2 departments 9 Find the items delivered for which there have been no sales 10 Find the name of the highest paid employee 35 11 Find the names of employees who make less than the average salary 12 List the number of employees for each department 13 Who earns the lowest salary 14 List the average salaries by department in descending order 36 Querying a Unary relationship Departmental Assignments DEPT IDEPTNAMEI DEPTFLOORI DEPTPHONE EMPNO Account1ng Management Marketing Personnel Purchasing EMP 4 5 l l 4 5 2001 l 2002 2 2005 9 2004 7 EMPNO EMPFNAM EMPSALARY DEPTNAME BOSSNO 1 Alice 2 Ned 3 Andrew 4 Clare 5 Todd 6 Nach 7 Brier 8 Sarah 9 Sophie 75000 Management 45000 Marketing 25000 Marketing 22000 Marketing 38000 Accounting 22000 Accounting 43000 Purchasing 56000 Purchasing 35000 Personnel amp PR Draw a normalized ER model for this data Write the SQL statements necessary to create this database 1 2 2 l 5 l 7 l 37 Write S QL to obtain the following information 1 Find the departments where all employees earn less than their boss 2 Find the names of all employees who are in the same department as their boss as an employee 3 List the departments with an average salary greater than 25000 4 List the departments where the average salary of the employees of each boss is greater than 25000 5 List the names and managers of the employees of the Marketing department who have a salary greater than 25000 6 List the names of the employees who earn more than any employee in the Marketing department 38 VII Physical Design 1 You are working for a large global corporation that sells personalized gift baskets There is a data communications network that links a computer at corporate headquarters in London with a computer at each retail outlet There are 50 stores with an average of 75 employees per store There are 12 departments in each store Assume o A daily schedule of employee s hours is kept for 7 months 0 The store manager for each store updates the employee work schedule about 5 times an hour 0 The headquarters handles all payroll checks 0 Corporate HQ handles all information about store managers and store managers handle all information about their employees The relations look like STOREStore Id Region Managerild SquareiFeet EMPLOYEEEmp Id StorefId Name Address DEPARTMENTDept StorefID Managerild SalesiGoal SCHEDULEDept Emp Id hours Draw a composite usage map and make recommendations about denormalizing partitioning and indexing 39 2 A medical clinic has the following relational schema for its data PERSONperson71D name address DOB PATIENTPAJersonilD Contact PHYSICIANPHJerson71D specialty PERFORMANCEPAJerson71D PH Jerson ID Treatment Treatmentidate Treatmentitime CONSUMPTIONPAJersonilD Item Date Quantity ITEMItem Description Draw a composite usage map and make recommendations about denormalizing partitioning and indexing 40
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'