Need help with Database programming assignment help or Project Help? At Codersarts we offer 1:1 session with expert, Code mentorship, Course Training, and on-going development projects. Get help from vetted Machine Learning engineers, mentors, experts and tutors.
This project is to develop a database system for Targeted Financial – a hypothetical financial advisory company. Targeted Financial provides financial advisory services to its customers. Customers of can be individuals or companies. Individual customers will have personal profiles, while company customers will have company profiles. So, we can think – Persons are Customers and Companies are Customers. Targeted Financial has financial advisors who provide the services (Advisors are Employees). They do provide trainings to individual customers based on pre-designed training in-house. In most cases, a training session is organized for multiple individual customers.
Customers will be encouraged to invest in Bank/Project Bonds or Stock markets. The following tables are identified by the previous database developer who just resigned – and you are responsible to complete the database development project.
Instructions:
Complete the design and normalization of the tables. Add more tables and columns are deemed important.
Use draw.io online tool to draw the ER diagram showing all the cardinality and optionality of the relationships
Identify all Primary and unique keys, all kinds of constraints; for all foreign keys, apply any cascading, set null, set default or no action options as necessary.
Write T-SQL script to create all the tables enforcing all the constraints
Use Alter statements to add columns and constraints and modify definitions of some tables (At least 10 Alter statements are required)
Data Insert: - This instruction applies only to part of the database objects – please make sure to insert data per the design
Insert 10 employees of which 4 are investment advisors
Insert 15 investment options – 10 Stock and 5 Bond
Insert 20 customers in total
15 individuals and 5 companies
From the above customers – 7 individuals should invest in at least 2 opportunities
3 individuals should invest in 1 investment option
5 individuals didn’t invest in anything so far
4 companies should invest in at least 2 opportunities, while 1 company invested in only one investment option
Insert 7 TrainingCourses
Insert 12 trainingSessions
Use update statements to change some data values (at least 10 update statements are required)
Stored procs, views, functions and triggers
Create a stored proc/view that returns
all customers – individuals and companies who have ever invested with their investments (both Bond and Stock)
individual customers who have ever got trainings with the training name and time
individual customers who have never got training
trainingCourse which are never offered
detail information of employees which are Advisors
stocks which are not yet invested in
bonds which are not invested in
the total amount of salary paid to all employees in the year 2017 – for this one you need to have salary column in the Employee table that shows annual salary of the employee
Create a stored proc that takes
customerId and returns the details of the customer and its investment so far
bondId and returns the bond name and the total number of investments in the bond
employeeId and returns the total amount he/she is paid in the year 2017
specialityId and returns all employees or advisors with that specialty – specialityId should be one column of the Employee table (if not given above, please add column)
Create a stored proc that inserts data into all tables
Employee, Bond, department and stock tables
Create trigger:
That takes audits of Bond and Stock tables – up on delete (first create the Audit tables)
That archives Employees when they are terminated from the company – (first create EmployeeArchive table with all columns of the Employee table and terminatedDate)
That archives bond and stock investment options when they are no more needed and deleted (Since this will contradict with b above, you need to drop the previous trigger to create this.)
Checks recruitment of Employees - if the Employee left our company, it should be at least two years before allowing them to be recruited again. Check with their SSN