ywkZFGzD

1**Objectives** :
2
3Write all the statements necessary. Paste the statements directly after the assignment.
4You work with Postgresql RDBMS.
5
6**Instructions** :
7
8Create a **database** that will be used for an app : **family duties**
9
10 ! => all the relations must have a primary key
11
121. **Create** the database whith a title that reflects what this database for this app.
13
14solution:
15CREATE DATABASE family_duties;
16
172. **Create** a **table** named **family** with
18comment: shouldn't this be **families**?
19
20  * name of the family, max 50 characters.
21  * creation date; the timstamp must precise the timezone.
22
23solution:
24CREATE TABLE family (
25  id serial PRIMARY KEY,
26  family_name varchar(50),
27  creation_date timestamptz DEFAULT now(),
28);
29
303. **Add** 4 families in the database :
31
32  * Loop
33  * Ifelse
34  * Break
35  * Return
36
37solution:
38INSERT INTO family (family_name) VALUES ('Loop');
39INSERT INTO family (family_name) VALUES ('Ifelse');
40INSERT INTO family (family_name) VALUES ('Break');
41INSERT INTO family (family_name) VALUES ('Return');
42
434. **Add** a constraint on the family name : each entry must be unique.
44
45solution:
46ALTER TABLE family ADD CONSTRAINT unique_name UNIQUE(family_name);
47
485. **Create** a table **members** with
49
50  * a name : last name, first name
51  * date of birth,
52  * Genre : one letter
53  * the family is belong to - a foreign key
54
55solution:
56CREATE TABLE members (
57  id serial PRIMARY KEY,
58  first_name varchar(50),
59  last_name varchar(50),
60  date_of_birth date,
61  genre varchar(1),
62  belongs_to varchar(50) REFERENCES family (family_name)
63);
64
656. **Add** a check on the genre to ensure that the value is M, F or O and nothing else.
66
67solution:
68ALTER TABLE members ADD CONSTRAINT valid_genre_chk CHECK (genre = ANY(array['M','F','O']));
69ALTER TABLE members ALTER COLUMN genre SET NOT NULL;
70
715. **Insert** some datas in this relations : Make to draw a relationship between the relations. Describe this relationship.
72
73  Loop family
74
75    °Loop Robert, 1973-12-25, M
76    °Loop Martha, 1978-09-18, F
77    °Loop Ingrid, 2001-10-10, F
78
79  Ifelse family
80
81    °Ifelse Jean, 1974-02-29, M
82    °Ifelse Jeanne, 1972-04-25, F
83    °Ifelse Maurice, 1998-02-12, M
84    °Ifelse Jean-Jacques, 2001-10-10, M
85    °Ifelse Monique, 2002-25-09, F
86    °Ifelse Jackson, 2007-08-07, M
87
88  Break family
89
90    °Break Paul, 1990-23-06, M
91    °Do Katharina, 2000-31-12, F
92    °Un'less Kathy, 2016-02-03, F
93    °Break-Do David, 2018-18-03, M
94
95  Return family
96
97    °Return Amanda, 1998-14-08, O
98    °Loop Engrid, 2001-10-11, F
99
100comments:
101-- the exercise calls for adding a row with these values into our database ('Ifelse', 'Jean', '1974-02-29', 'M');
102-- 1974 was not a leap year, the chosen corrective action was to correct the date to '1974-03-01'
103
104-- the exercise calls for adding a row with these values int our database ('Ifelse', 'Monique', '2002-25-09', 'F')
105-- the chosen corrective action was to correct the date to '2002-09-25'
106
107-- the exercise calls for adding a row with these values int our database ('Break', 'Paul', '1990-23-06', 'M')
108-- the chosen corrective action was to correct the date to 1990-06-23'
109
110-- the exercise calls for adding a row with these values int our database ('Do', 'Katharina', '2000-31-12', 'F')
111-- the chosen corrective action was to correct the date to 1990-12-31'
112
113-- the exercise calls for adding a row with these values int our database ('Break-Do', 'David', '2018-18-03', 'M')
114-- the chosen corrective action as to correct the date to '2018-03-18'
115
116-- the exercise calls for adding a row with these values int our database ('Return', 'Amanda', '1998-14-08', 'O')
117-- the chosen corrective action was to correct the date to '1998-08-14'
118-- in all of the above cases the corrective action is questionable, since its possible that we still have incorrect addresses in our DB
119-- to improve the exercise we could decide what to do with incorrect values explicitly
120
121solution:
122INSERT INTO members
123(last_name, first_name, date_of_birth, genre, belongs_to) VALUES
124  ('Loop', 'Robert', '1973-12-25', 'M', 'Loop'),
125  ('Loop', 'Martha', '1978-09-18', 'F', 'Loop'),
126  ('Loop', 'Ingrid', '2001-10-10', 'F', 'Loop'),
127  ('Ifelse', 'Jean', '1974-03-01', 'M', 'Ifelse'),
128  ('Ifelse', 'Jeanne', '1972-04-25', 'F', 'Ifelse'),
129  ('Ifelse', 'Maurice', '1998-02-12', 'M', 'Ifelse'),
130  ('Ifelse', 'Jean-Jacques', '2001-10-10', 'M', 'Ifelse'),
131  ('Ifelse', 'Monique', '2002-09-25', 'F', 'Ifelse'),
132  ('Ifelse', 'Jackson', '2007-08-07', 'M', 'Ifelse'),
133  ('Break', 'Paul', '1990-06-23', 'M', 'Break'),
134  ('Do', 'Katharina', '2000-12-31', 'F', 'Break'),
135  ('Un''less', 'Kathy', '2016-02-03', 'F', 'Break'),
136  ('Break-Do', 'David', '2018-03-18', 'M', 'Break'),
137  ('Return', 'Amanda', '1998-08-14', 'O', 'Return'),
138  ('Loop', 'Engrid', '2001-10-11', 'F', 'Loop');
139
140In the tables' current state the members table contains mostly members that belong to one family only.
141Even so, there exists a One-to-Many relationship between the tables. This is evident from the fact that Ingid Loop belongs
142to multiple families ('Loop', 'Return')
143
144 5. **Delete** the last input : there is an error -> Loop Engrid is the same person as Loop Ingrid.
145    Make sure that Loop Ingrid is also a member of the Break family.
146
147comments: we're assuming that the question means "Make sure that Loop Ingrid is also a member of the Return family"
148
149solution:
150identify the duplicate row, the below returns a row with a id-value of 15
151SELECT *
152  FROM members
153 WHERE first_name = 'Engrid'
154
155delete the duplicate row
156 DELETE
157   FROM members
158  WHERE id = 15;
159
160make sure Ingird Loop is part of the Return family:
161Since we don't have a join table, we manually add Ingrid Loop to the Return family.
162This introduces some duplication
163
164INSERT INTO members (last_name, first_name, date_of_birth, genre, belongs_to) VALUES
165  ('Loop', 'Ingrid', '2001-10-10', 'F', 'Return');
166
167 6. **Change** the date of birth for Do Katharina; she was born on april.
168
169 solution:
170 a. make sure we have the right row, b. update
171
172 a.
173 SELECT *
174   FROM members
175  WHERE first_name = 'Katharina' AND last_name = 'Do';
176-> this returns a row with an ID of 11
177 b.
178 comments: note the change from 31 to 30 due to April only having 30 days
179 UPDATE members
180    SET date_of_birth = '2000-04-30' WHERE id = 11;
181
182 7. You realize now that foreign keys don't prevent from NULL values.
183    You decide to **add** a constraint on the FK to ensure no NULL values can be entered in your DB.
184
185solution:
186ALTER TABLE members ALTER COLUMN belongs_to SET NOT NULL;
187
188 8. By the way, you get a new idea. You want now to **add** a new column **responsibilities** to the **members** relations.
189    This column will contain a letter and a sign, no more (eg 'A+').
190    Be sure that the letter could only be 'A, T or C' and the sign '+ or -'.
191
192comments: assuming case-sensitivity
193solution:
194ALTER TABLE members
195  ADD COLUMN responsibilities varchar(2) CHECK (responsibilities ~ '[ATC][+-]');
196
197
198 9. It's time to inspect your db. Make views according to the requirements below :
199 10. View the total number of members.
200
201solution:
202SELECT count(*) AS "total members"
203   FROM members;
204
205 11. View the total numbers of members for each family.
206
207solution:
208SELECT family_name, count(*) as members
209  FROM family INNER JOIN members ON belongs_to = family_name
210 GROUP BY family_name;
211
212 12. View all the members born strictly after 2000.
213
214 solution:
215 SELECT *
216   FROM members
217  WHERE (SELECT extract(year FROM date_of_birth)::int > 2000);
218
219 13. View all the adults members and sort them by genre.
220
221solution:
222SELECT first_name, last_name
223  FROM members
224 WHERE date_part('year', age(date_of_birth)) >= 18
225
226 14. View if two members are born on the exact same date.
227
228solution:
229SELECT first_name, last_name
230  FROM  members
231 GROUP BY first_name, last_name
232HAVING count(date_of_birth) > 1;
233
234 15. View the members that are in more than one family.
235
236solution:
237SELECT first_name, last_name
238  FROM members
239 GROUP BY first_name, last_name
240HAVING count(belongs_to) > 1;