The University of Melbourne
School of Computing and Information Systems INFO20003 Database Systems
Practice data modelling task: The Victorian Parliament
The Victorian Parliament is made up of a lower house (the Legislative Assembly) and an upper house (the Legislative Council). Every four years, Victorians go to the polls to elect one MP (member of Parliament) for their Legislative Assembly district, as well as the five MPs who will represent their region in the Legislative Council.
Copyright By PowCoder代写 加微信 powcoder
Most people will be familiar with the role of MPs in Parliament’s two houses, debating and voting on proposed laws. But MPs also serve on a range of committees, which develop and analyse the proposed laws in detail or hold inquiries into various aspects of government.
Figure 1: The Victorian Legislative Council in session.
A team of political scientists have asked you to develop a simple database to store information about current members of the Victorian Parliament. Each MP’s title, first, middle and last name, and date of birth must be stored. The database must also contain the year each MP was first elected to Parliament, and the name of the political party they belong to (if any).
The database needs to store information about each district and region in Victoria. For each district, the name, demographic type (urban, rural or mixed), population, and average weekly household income is required. Each region has a name and a demographic type (metropolitan or regional), and is geographically made up of 11 districts. The database must record the current MP(s) representing each district and region.
If an MP resigns, is disqualified or dies, their seat may be vacant for a period of time until a by-election is held. Additionally, an MP cannot be a member of both the upper and lower house at the same time.
Most committees are “joint” (they include members of both houses of Parliament), but some committees are drawn from the lower or upper house only. The committee’s name, the URL of its webpage, its contact email address, the MPs who currently belong to the committee, and the names and roles of the committee’s administrative staff members must be stored.
Page 1 of 4
© 2018 The University of Melbourne
INFO20003 Practice data modelling task: The Victorian Parliament Page 2 of 4
1. Develop a conceptual ER model for this scenario. You may use Chen’s notation or Crow’s foot notation, but do not mix the two.
2. State three constraints given in the requirements analysis that could not be included in a conceptual ER model because of a lack of suitable notation.
3. Resolve your conceptual model into a physical model for a MySQL database server, using Crow’s foot notation.
4. Suppose you have just created the database, and your brand-new tables are now empty. You would like to add some initial data to test it out. Write appropriate INSERT statements (taking care of the order in which they are written) to add an MP named Rollinson, born 8 July 1976, who represents the district of Polwarth for the National Party. She was first elected in 2018, and currently serves on the Family and Community Committee (a joint committee). Polwarth is a rural district with a population of 59,969 and an average weekly household income of $1,157. It is part of the regional Western Victoria region. You may invent a URL and email address for the committee.
INFO20003 Practice data modelling task: The Victorian Parliament
Page 3 of 4
1. Here’s a sample conceptual model using Chen’s notation:
name population
demographic type
middle last
name date of birth
MP year first elected
average weekly income
demographic type
is part of
represented by
represented by
Committee staff member
LC, LA or joint?
2. The following constraints cannot be expressed with the conceptual model:
• A region must contain exactly 11 districts
• A region must have no more than 5 MPs (not exactly 5, as vacancies can occur)
• Each MP must belong to either a region or a district, but not both
Since the conceptual model does not have any information about data types, the following constraints also cannot be expressed:
• Any mandatory (NOT NULL) constraints on attributes (not “optional” or “NULL” – this is the absence of a constraint) (e.g. MP’s last name is mandatory)
• Any domain constraints on attributes (e.g. a district’s demographic type must be one of ‘urban’, ‘rural’ or ‘mixed’)
INFO20003 Practice data modelling task: The Victorian Parliament Page 4 of 4
4. INSERT INTO Committee VALUES (‘Family and Community Committee’, ‘joint’, ‘https://parliament.vic.gov.au/fccom’,
INSERT INTO Region VALUES (‘Western Victoria’, ‘R’); INSERT INTO District VALUES (‘Polwarth’, ‘R’, 59969, 1157,
‘Western Victoria’);
INSERT INTO MP (MPID, FirstName, MiddleNames, LastName, DateOfBirth, YearFirstElected, Party, House, DistrictName) VALUES (1, ‘Andrea’, ‘Maud’, ‘Rollinson’, ‘1976-07-08’, 2018, ‘National Party’,
‘LA’, ‘Polwarth’);
INSERT INTO MPSitsOnCommittee VALUES (1, ‘Family and Community Committee’);
Note that Ms Rollinson does not represent the region of Western Victoria, so we did not set her RegionName value.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com