Database DesignDatabase DesignDatabase DesignCmpE 226 Practice Problems Practice Problem (09)________________________________________________________________________1. Design a database with at least 10-12 of tables or ((constraint tables. The constraint tables should use the full power of linear constraints. (For example, the equality constraint x =1 does not use the full power of linear constraints. So don ’t use only equality constraints in your database.)) The tables should contain areasonable number of tuples or (constraint tuples), not too few and not too many. 2. Prepare traditional class diagram for the following problems showing at least 10 relationships among the following object classes, including associations, aggregations, and generalizations. Show multiplicities in your diagrams. Your model should have 3-5 attributes and 3-5 operations per class. Use association and role names when needed. As you prepare the diagrams, you may add additional object classes.3. If you use MLPQ, find at least 4 iconic queries or sequence of iconic queries.Explain what they mean and write them down in the style of the examples in theMLPQ system manual. (Iconic queries are shown in Figure 1 MLPQ GraphicalInterface in the MLPQ Specifications, such as Intersection, Union, Difference,Max, Min, etc.)4. Find at least 4 SQL queries of each of the following kind: Basic, Aggregation, Sets,and Nested. (That is, a total of 16 queries.)________________________________________________________________________OUTSOURCINGINNOVATIONAbstractOutsourcing has been taking place for a long time now, but is becoming anessential part of today’s global economy. In the earlier days, outsourcingused to be associated to moving customer support call centers to countrieslike India, etc. But with increasing competition, demand and growth intechnology, now Research & Development, IT Operations, Engineering Design,Medicare, and Software Development are also being outsourced to other countries.DomainCompanies sub-contract to save on costs and to effectively and efficiently perform many operations. The contract companies are globally situated. Companies may choose to send the jobs off shore or to retain it at its homeland depending on various controllable and non-controllable factors.DescriptionIn this project, we will build a database model which would allow us to retrievedata and analyze contract companies in various countries.Objectives:- Impact of outsourcing on economy- Determine the effectiveness of maintaining services in US- Reduce costs by deciding operations that have to be outsourced/insourced in the long term.- Risk management- Impact on education & jobsRequirements1. Calculate operation costs, wages at different regions for both outsourced and insourced companies2. Determine risk for the operation(s) at different regions and see if we can move critical jobs to low risk regions3. Calculate the trends in number of graduates available in different region and the correlation it has with the number of jobs available4. The regions with least operation costs5. The operations that are mostly outsourced and analyze future outsourcing trends6. The contribution of this industry towards the country’s economy7. Customer satisfaction at different regions and the key contributors to higs satisfaction levels8. The movement of specific types of jobs from one region to the other over a period of time and the reason behind such a moveRelation or entities names and attributesRegion(regionId, countryId, x, y)This defines various regions over x and y co-ordinatesregionId – unique ID Identifying each regioncountryId – Id of the countryx – x coordinate value for the regiony – y coordinate value for the regionCompany(regionId , year, noOfOutsourcedCompanies, noOfInsourcedCompanies)This has a total number of companies and the outsourced and the insourced companies of each region per yearregionId – IDF Identifying the regionnoOutsourcedCompany – number of outsourced companiesnoInsourcedCompany – number of insourced companiesyear – the year during which the particular data was applicableJob(regionId, year, jobRange, operationId)This has the jobs range that are present for each operation for each region for each year regionId – ID Identifying the regionyear – the year during which the particular data was applicablejobRange – range of jobs available for the particular operation operationId – ID Identifying the operationWage(wageRange, noOutsourcedCompany, noInsourcedCompany, regionId, year)This has the number of outsource companies and insourced companies that fall within a wage range for each region for each year wageRange – range of wages noOutsourcedCompany – number of outsourced companiesnoInsourcedCompany – number of insourced companiesregionId – IDF Identifying the regionyear – the year during which the particular data was applicableOperation(operationId, operationCost, noOutsourcedCompany, noInsourcedCompany, regionId, year)This has the number of outsourced and insourced companies with various operations ( IT DEV, customer Support, R &D ) operationId – ID for the operationoperationCost – range of operation costsnoOutsourcedCompany – number of outsourced companiesnoInsourcedCompany – number of insourced companiesregionId – IDF Identifying the regionyear – the year during which the particular data was applicableEducation(noOfGraduate, level , regionId, year)This has the number of graduates each year for a specific level of study for each regionnoOfGraduate – no of graduates available / passinglevel – the level of education that can be achieved (constraint column: level of education can be represented by numbers. On a scale of 1-10, 10 being the highest level, say PhD.)year - the year during which the particular data was applicableCustomerSatisfaction(satisfactionRate , NoOutsourcedCompany, NoInsourcedCompany, regionId, year)This has the number of outsourced companies and insourced companies that fall in a satisfaction rate of 1 to 10 scales (10 being the highest)satisfactionRate – the rate of satisfaction that can be achieved (constraint column rate of satisfaction can be represented by numbers. On a scale of 1-10, 10 being the highest rate of satisfaction, say fully Satisfied.)noOutsourcedCompany – number of outsourced
View Full Document
Unlocking...