In company , information about different employees and projects is held. Each employee has first and last name (both between 5 and 20 letters, inclusive), year salary , manager and department. Each department has unique name between 10 and 50 letters, inclusive. Each employee works on many projects. Each project has name between 5 and 50 letters, inclusive, and employees working on it. Each employee also has starting and ending** date for each of his/her projects . Every single piece of information is mandatory , except manager – some employees do not have one. Additionally, every employee has to report every day when he/she got to work. Each report has time** of reporting.
Design a database schema " Company" in SQL Server to keep the information about employees , departments , projects and reports. Ensure data integrity is fulfilled.
Provide a SQL script for your database schema (without the data), a backup (.bak file) from the database and a simple documentation of the relationships between the tables. Additionally, provide a picture of the design diagram describing your created database.
Use C# to implement an application for generating random sample data in the company. Create at least:
- 100 departments
- 5 000 employees – each employee has department, 95% of them have managers and their salary is between $50 000 and $200 000, inclusive. There must be no cycles in the management tree –example for a cycle is Pesho's manager is Gosho, Gosho's manager is Ivan and Ivan's manager is Pesho.
- 1 000 projects – on each project there are working between 2 and 20 employees, inclusive – average of 5. Ending date is always after starting date (you don't say) and ending date may be in the future.
- 250 000 reports – add average of 50 reports per employee.
* You are free to use an ORM framework by choice or plain ADO.NET.
* Adding a lot of data may be slow, depending on your implementation, so be patient. Consider turning off the automatic change tracking in Entity Framework, if you use it.
* All text can be random string with valid length depending on the field.
Write three queries in native SQL:
- Get the full name (first name + " " + last name) of every employee and its salary, for each employee with salary between $100 000 and $150 000, inclusive. Sort the results by salary in ascending order.
- Get all departments and how many employees there are in each one. Sort the result by the number of employees in descending order.
- Get each employee's full name (first name + " " + last name), project's name, department's name, starting and ending date for each employee in project. Additionally get the number of all reports, which time of reporting is between the start and end date. Sort the results first by the employee id, then by the project id. (This query is slow, be patient!)
Provide three .sql files with the three queries in them.
Create a "cache" table for the third query from problem 3 and save all the results there for future querying. Provide the .sql file, containing the stored procedures which creates the table and inserts the data. You should write two stored procedures – one for creating the table and one for updating the data.
In different cities , cars from different manufacturers and dealers are sold. Each manufacturer has name (mandatory with maximum of 10 symbols). Each car has manufacturer, model (maximum of 11 symbols), transmission type, year, price and dealer. All fields are mandatory. Transmission type can be manual or automatic. Dealers have name (mandatory with maximum of 50 symbols) and cities, in which they sold cars. Each city has name (mandatory with maximum of 10 symbols). Each city and each manufacturer is unique by their name.
Design a database schema " Cars" using Code First approach with Entity Framework.
Write a C# program to parse a number of JSON files with name format "data.number.json" holding a set of cars' information in the format given below and insert them into the database :
* You are free to use a JSON parser by choice (or to parse the JSON without using a parser).
You should correctly import the cars, manufacturers, dealers and cities into the DB. Keep in mind that the cities and manufacturers are unique. If some city or manufacturer already exists in the database, it should be reused (no duplicates are allowed).
* You are free to use an ORM framework by choice (in code first).
Implement a C# program for searching for cars** by given conditions . It should be able to process a sequence of queries from the XML file queries.xml** in the following format:
Each query has different output file, provided in the "OutputFileName" attribute.
The queries can have two types of parameters - ordering and where clauses.
Ordering the results can be done by the following fields: Id, Year, Model, Price, Manufacturer (its name) and Dealer (its name).
Where clauses can be one or many. If many where clauses are provided, use "and" as logical operator between them. Where clauses have two attributes for defining the query – "PropertyName" and "Type". "PropertyName" defines by which car property the search should be done. "Type" defines the type of comparer to use in the search. The following list gives you the available property types and comparers for each of them:
- Id (number) -> Equals, GreaterThan, LessThan
- Year (number) -> Equals, GreaterThan, LessThan
- Price (floating point number) -> Equals, GreaterThan, LessThan
- Model (text) -> Equals, Contains
- Manufacturer (its name, text) -> Equals, Contains
- Dealer (its name, text) -> Equals, Contains
- City (its name) -> Equals
"GreaterThan" and "LessThan" does not include equal elements.
Write the results in the corresponding XML files in the following format:
