CS330 Winter 2024Assignment 2
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CS330 Winter 2024Assignment 2
Due Wednesday, February 28 at 5 pm.
Coverage
The questions in this assignment are based on the material covered up to and including the end of Step 3 of Relational Database Design (slide 221 or 222 depending on your section).
Submission Requirements
See Assignment 1 for details on how to submit. Depending on the question, the submission format will either be
• text that you can type in directly or copy and paste from another document or
• an image file. Crowdmark only accepts JPG, PNG and PDF format.
Recall that Crowdmark does not accept HEIC format.
WeSecureYou Ltd
This and future assignments will be investigating the needs and solutions for the company WeSecureYou.
WeSecureYou Ltd was formed by four UW grads in 2018 who had an idea for an innovative IT consulting firm that would let the group leverage their degrees in a way that combined skills from a variety of disciplines.
Ling Chang and Tetyana Galavicha are Computer Science grads who brought technical talent. Rahman Raufdeen was in Financial Analysis and Risk Management and brought considerable financial acumen. Rounding out the group, Josh Brightson was in the Global Business and Digital Arts program and is the innovation spark in the group. Together the four made a formidable team combining business acumen, with solid IT skills with a good understanding of the market and how to run a business.
Since launching, WeSecureYou has grown to have 25 full time employees joined by another 10 co-op students at any one time. All four founders recognized UW’s coop program as a reliable source for talent. Together this team serves over 300 companies.
WeSecureYou provides IT consulting and support services to a niche market – independent financial advisors. WeSecureYou has product and service offerings that ranges from consulting (helping clients solve an IT problem), to providing onsite or telephone support lines for clients who wish to manage their IT infrastructure in-house, right through to full turn-key solutions where WeSecureYou provides and supports a full suite of MIS solutions running on servers maintained by WeSecureYou and specifically tailored to the needs of financial advisors.
WeSecureYou differentiates itself from in competitors in two notable ways. First, recognizing that many users of technology do not understand all the terminology and concepts of the IT world, nor can they always find the source of the problem, WeSecureYou provides each of their clients with a single point of contact, a customer service representative, each with their own telephone extension, rather than have a single helpline that all customers must use. This allows the client and their customer service rep to get to know each other. Whatever the customer’s need, they contact their customer service rep who gathers the details. When they communicate with their client, the reps always use non-technical language to explain the situation and the options available. They are specifically hired for their personal disposition and their ability to explain technical concepts.
Secondly, as their company name implies, WeSecureYou’s value proposition is when supporting their client’s IT needs, WeSecureYou goes a crucial step further and focusses heavily on the security and privacy of client systems. They set up locked and well-managed machines and fine- tune their clients’ firewalls to block risky content in emails and on webpages.
The communication skills of their customer service reps and the company’s expertise and focus on security and privacy has allowed WeSecureYou to differentiate themselves from the many other small IT consulting and service providing firms working in this crowded market.
Representative engagements the firm has undertaken in the past year include:
• preparing an IT Risk and Threat Assessment for 10-person financial advisory team working in downtown Ottawa,
• provisioning 20 endpoint devices for a small financial advisory team in London, Ontario including image loading, testing and installing the necessary network connectivity at three office locations,
• onboarding a 50-person financial advising firm in Winnipeg who instead of installing their own servers have engaged WeSecureYou to provide and support a fullback-end service solution.
(SAAS).
WeSecureYou has just hired you as their latest coop student. During the term you will take on three projects for WeSecureYou.
Creating a Database for WeSecureYou
For the first of those projects, WeSecureYou would like to create a database to manage some basic service and billing information about its clients.
After interviewing key employees, you learn that the company maintains the following attributes in a flat file:
|
CNum |
a unique customer number for each customer |
|
CName |
the customer’s name (although rare, different customers can have the same name) |
|
CStr |
the customer’s head office street address for billing purposes |
|
CProv |
the official postal code abbreviation for the customer’s province (e.g., ON = Ontario) |
|
TNum |
a unique ticket number associated with work done for a customer |
|
TDate |
the date on which a ticket was created |
|
SCode |
a one-letter code that describes the type of service provided to the customer. It can be one of three types: C, I or T for Consulting, IT Support, Turnkey respectively. |
|
SType |
the full name of the type of service, i.e. one of Consulting, IT Support or Turnkey. |
|
SCost |
the cost of services provided for a particular SType |
|
RNum |
each WeSecureYou representative has a unique employee number |
|
RName |
the name of the WeSecureYou representative |
|
RExt |
the telephone extension of the WeSecureYou representative. |
|
SDetails |
a field where reps can provide a textual description of the service provided |
From CS330 you are aware that this type of flat file structure results in redundancy, increasing the risk of data integrity problems.
Other information that you gathered by interviewing key employees include:
• Customers can contract for more than one type of service (i.e., C, I and T from above).
• A work ticket might have more than one type of service listed although it is possible to also have work tickets where only one type of service was provided (e.g., IT Consulting)
• Work tickets are generated by reps after they provide service. Therep decides on the amount to charge.
• The system needs to keep track of the province abbreviations so that there is away to lookup and discover that AB = Alberta and BC = British Columbia.
1. Create an ER diagram including
a) the entities and the relations,
b) the cardinalities of the relations.
There is no need to show whether the participation is partial or total. Submit your diagram to Crowdmark in JPG, PNG, or PDF format. [12 marks]
2. List all the functional dependencies in the diagram. Input your answer into Crowdmark as text. Crowdmark should allow the arrow symbol, i.e. → , which you can copy an paste from this document. If not, you can use -> instead. [6 marks]
3. List all the tables in BCNF. Input your answer into Crowdmark as text. Crowdmark does not
allow underlined text, so use ALL CAPS to identify the primary key and use italics to represent any foreign keys. In Crowdmark putting an asterick at the beginning and at the end of some text will display it as italics. [6 marks]
2024-02-25