Description
CSCI-GA-2433 Homework 1
1. You need to have a CIMS computer account. If you do not have one yet please follow https://cims.nyu.
56 edu/webapps/content/systems/userservices/accounts/obtain to request an account. Your request
57 should generally be automatically granted.
58 2. Read the syllabus for the class, 2433.pdf. Decide whether you fully understand it. If you do not, state what
59 needs to be clarified for full understanding.
60 3. Please read the file University01.pdf and follow the instructions in it.
61 4. As part of this assignment you will make sure that you are able to produce an ER diagram and an associated
62 text file when required. In this assignment,
63 • Reproduce the diagram ER01.pdf (it may be difficult to see, but note that the line under C is dotted)
64 and modify the file text01.txt, both of which are part of this archive.
65 The diagram has the following shapes
66 • rectangles (use the not-rounded version)
67 • diamonds (rhombuses)
68 • rectangles with diamonds inside
69 • ellipses
70 • triangles
71 The diagram has the following types (formats) of lines
72 • regular
73 • double
74 • dashed
75 • with and without arrows
76 The diagram has the following types (formats) of text
77 • regular
78 • underlined
79 • underlined by dots
80 To produce your diagram, use draw.io https://www.draw.io/, which is free and does not require any
81 installation, but installation can generally be done, so you can work locally, if you like. Use the Basic set of
Typeset: 2021-09-11T13:30:19Z MD5: EB103A808DD6CE89F3E55B42F3FA2766
Page 3 of 3
82 templates and in it use the Entity Relation template; if it does not have what you need to complete the
83 diagram, use also the General template. Try to fit it on one page. The native format you will use is drawio.
84 Start with the given ER01.drawio. Your drawing diagram should be a duplicate of the one in this assignment
85 but put your own NetID in the cloud drawn there.
86 Don’t be fanatical about the faithfulness of the duplication. It is enough to make sure that the various shapes
87 are the same as the ones given and placed roughly in the same locations, that the lines are recognizable as
88 the ones given, that the shapes are connected as in the diagram (note particularly that sometime connections
89 are to vertexes and sometime to edges so reproduce that), and that the text in the shapes is formatted as
90 given. But do try to be neat.
91 If the system does not allow you to upload ER01.drawio, state so in Item 1d of Section 3.
92 3 What to submit
93 Please upload 3 files, named exactly as specified and in the format exactly as specified.
94 1. text01.txt in the text format, by modifying the file in this assignment.
95 In that file
96 • Replace the Metadata with your information
97 • Leave the Annotations as they are
98 • Place your responses to items Item 1, Item 2, and Item 3 of Section 2.2 by placing appropriate text
99 after the item label as listed below (so your first item will labeled “(a)”):
100 (a) Do you have a CIMS account or have you requested such an account? If one of the above holds
101 just write “Yes”.
102 If neither holds, explain why.
103 (b) Have you read and understood the syllabus? If that’s the case just write “Yes”.
104 If that’s not the case state that you are contacting the instructor by email, and send email to Zvi
105 Kedem, mailto:zk1@nyu.edu with the subject line “2433 questions on syllabus” listing any issues
106 that need clarification.
107 If neither holds explain why.
108 (c) Have you read the case study University01.pdf and have you considered what it could mean? If
109 that’s the case, just write “Yes”.
110 If you have not done so, explain why.
111 (d) If you cannot upload any of ER01.drawio, state so. Otherwise leave this item empty.
112 2. A file with a diagram in the native format as produced by draw.io. The file should be called ER01.drawio.
113 3. A file with a PDF version of the diagram you have produced. The file should be called ER01drawio.pdf.
Typeset: 2021-09-11T13:30:19Z MD5: EB103A808DD6CE89F3E55B42F3FA2766
CSCI-GA-2433 Homework 2
Assignments
57 1. Please read the file University01.pdf and review the implementation of that application as an ER diagram
58 in Unit 2.
59 2. For the third-person singular pronoun I will generally use “it” and its declensions.
60 Some words are in bold. This has no semantic implications and they are written in this way to make the
61 reading easier.
62 Consider the application described in Section 2.2.1. Do not make any assumptions about the application
63 beyond the specifications listed.
64 Following the notation we used in class, using draw.io and the shapes provided by it, produce an ER diagram
65 for the application. Do not use any other notation. For example, you may not use cardinality constraints in
66 your diagram. Start with the given ER02.drawio and put your own Net ID in the cloud there.
67 Because the software you will use has slight differences compared to what we did, please consult the file
68 NotationForER.pdf so that you know how to produce your diagram.
69 Do not optimize your design, just follow the specification given.
70 Anything that cannot be specified in your ER diagram, put as annotations in text02.txt file, as described
71 in Item 1 of Section 3, starting with 1. and continuing as needed.
72 Do not put anything in the annotations that can be reflected in the ER diagram, make sure that the diagram
73 reflects that. The idea is for you to think what’s needed and where to put it: the diagram or the annotations.
74 2.2.1 The Application
75 Note that you cannot make any assumptions that are not forced by the specifications. For example, if the
76 value of an attribute is not required to be known, you cannot assume that it is always known.
77 There are Companies. A Company may be Small or Big but cannot be both. Companies have
78 an attribute TIN (Tax Identification Number) and RegNumber (Registration Number) and they are
79 always known. No two Companies can have the same TIN and no two Companies can have the same
80 RegNumber.
81 A Company may have one or more Nicknames.
82 Small Companies may Help each other and we want to know which Small Company Helps which
83 Small Company. A Small Company may not Help itself.
Typeset: 2021-09-21T19:26:36Z MD5: C76AAA3A23CAA2BB6421A504FB531362
Page 3 of 3
84 There are Managers. Each has the attribute FamName (Family Name) and the attribute PerName
85 (Personal Name), which are always known. No two Managers can have both the same PerName and the
86 same FamName. Managers also have the attributes Salary and Bonus, which are always known. Bonus
87 is computed as 5% of Salary.
88 A Big Company has the attribute Value. For a Big Company there may be at most one Manager who
89 Supervises it. If a Manager Supervises a Company, that relationship has attribute Start.
90 The domains of the attributes have not been specified.
91 Start with the given ER02.drawio but put your own NetID in the cloud drawn there.
92 If the system does not allow you to upload ER02.drawio, rename your ER02.drawio to be ER02.txt. Then
93 upload ER02.txt, and state that you have done that in Item 1b of Section 3.
94 3 What to submit
95 Please upload 3 files, named exactly as specified and in the format exactly as specified.
96 1. text02.txt in the text format, by modifying the file in this assignment.
97 In that file
98 • Replace the Metadata with your information.
99 • State the required (and only the required) Annotations.
100 • State your responses by placing appropriate text after the item label as listed below (so your first item
101 will labeled “(a)”):
102 (a) Have you done what’s requested in Item 1 of Section 2.2? If yes, just write “Yes”.
103 If not, explain why.
104 (b) If you cannot upload ER02.drawio, state so. Otherwise leave this item empty.
105 2. A file with a diagram in the native format as produced by draw.io. The file should be called ER02.drawio.
106 3. A file with a PDF version of the diagram you have produced. The file should be called ER02drawio.pdf.
Typeset: 2021-09-21T19:26:36Z MD5: C76AAA3A23CAA2BB6421A504FB531362
CSCI-GA-2433 Homework 3
2.1 Description
52 The are 2 assignments. (1) To create an ER diagram with annotations; (2) To download, install, and open SQL
53 Power Architect.
54 Precise submission details are given in Section 3.
55 2.2 Assignments
56 1. Below is the description of the application under consideration.
57 For the third-person singular pronoun I will generally use “it” and its declensions.
58 Some words are in bold. This has no semantic implications and they are written in this way to make the
59 reading easier.
60 Do not make any assumptions about the application beyond the specifications listed.
61 Following the notation we used in class, using draw.io and the shapes provided by it, produce an ER diagram
62 for the application. Do not use any other notation. For example, you may not use cardinality constraints in
63 your diagram.
64 Because the software you will use has slight differences compared to what we did, please consult the file
65 NotationForER.pdf so that you know how to produce your diagram.
66 Do not optimize your design, just follow the specification given.
67 Anything that cannot be specified in the ER diagram, put as annotations in text03.txt file, as described in
68 Item 1 of Section 3, starting with 1. and continuing as needed.
69 Do not put anything in the annotations that can be reflected in the ER diagram, make sure that the diagram
70 reflects that. The idea is for you to think what’s needed and where to put it: the diagram or the annotations.
71 Note that you cannot make any assumptions that are not forced by the specifications. For example, if the
72 value of an attribute is not required to be known, you cannot assume that it is always known.
73 There are Companies. Companies have an attribute TIN (Tax Identification Number) and it is always
74 known. No two Companies can have the same TIN.
75 A Company Helps exactly one Company; a Company may Help itself. For such a Help, a Company
76 charges a Fee and its value is not necessarily known.
77 A Company may Own Stores and no two Companies may Own the same Store. Each Store has at78 tributes Number and Size. Number is always known and no two Stores Owned by the same Company
79 may have the same Number. If Size is known, it is at least 100,000.
80 There are Books. A Book has two attributes ISBN and Price. ISBN is always known and no two books
81 can have the same ISBN.
82 Stores Sell Books. If a Store Sells a Book, we may know the StartDate when that Store started
83 selling that Book.
84 Sales may be Approved by one or more Companies.
Typeset: 2021-10-03T02:46:21Z MD5:
Page 3 of 3
85 The domains of the attributes have not been specified.
86 Start with the given ER03.drawio but put your own NetID in the cloud drawn there.
87 If the system does not allow you to upload ER03.drawio, rename your ER03.drawio to be ER03.txt. Then
88 upload ER03.txt, and state that you have done that in Item 1b of Section 3.
89 2. You will need to use SQL Power Architect to specify relational databases in a future assignment. Please
90 download and install the latest version of the community edition from https://www.bestofbi.com/page/
91 architect_download_os. Make sure that you can open it.
92 You may encounter some difficulties running the software so here are possible workarounds.
93 (a) A number of students in a previous class could not make the Mac version work on Mac, but the
94 Unix/Generic version did work. Please use the link for direct download : https://www.bestofbi.com/
95 downloads/architect/1.0.9/SQL-Power-Architect-generic-1.0.9.tar.gz.
96 Detailed steps can be found here : https://www.bestofbi.com/page/architect-start
97 Also, if your Java version is too old you may need to update it. Please see https://www.java.com/
98 en/download/manual.jsp.
99 (b) Sometimes it is not possible to save a new file. This bug is intermittent. However, it was always possible to
100 open an existing file, modify it, and just save it. Therefore a blank file relationalDatabase.architect
101 is enclosed. You can just copy it to a directory of your choice and work with it.
102 State in text03.txt whether you have installed SQL Power Architect and were able to open it.
103 3. Modify text03.txt by putting in it
104 1. Your metadata
105 2. The list of the annotations as requested above
106 3. Have you done what is requested in Item 2 of Section 2.2? If that’s the case, just write “Yes”.
107 If you have not done so, explain why. And if you run into difficulties doing that part of the assignment
108 after applying all the workarounds described, also send email to Nancy Nigam, mailto:nn2163@nyu.edu,
109 explaining what the difficulties were.
110 3 What to submit
111 Please upload 3 files, named exactly as specified and in the format exactly as specified.
112 1. text03.txt in the text format, by modifying the file in this assignment.
113 In that file
114 • Replace the Metadata with your information.
115 • State the required (and only the required) Annotations.
116 • State your responses by placing appropriate text after the item label as listed below (so your first item
117 will labeled “(a)”):
118 (a) Have you done what’s requested in Item 2 of Section 2.2? If yes, just write “Yes”.
119 If not, explain why.
120 (b) If you cannot upload ER03.drawio, state so. Otherwise leave this item empty.
121 2. A file with a diagram in the native format as produced by draw.io. The file should be called ER03.drawio.
122 3. A file with a PDF version of the diagram you have produced. The file should be called ER03drawio.pdf.
Typeset: 2021-10-03T02:46:21Z MD5:
CSCI-GA-2433 Homework 4
2.1 Description
53 There are two assignments: (1) To exercise logging into Oracle and running a simple, prepared query: (2) To
54 create a relational implementation of an ER diagram.
55 2.2 Assignments
56 1. Start with this part. It is simple but if you run into difficulties they need to be resolved quickly.
57 Make sure that you know how to run SQL queries/commands on the Oracle systems at CIMS.
58 To do that, follow the instructions in How_To_Use_Oracle_At_CIMS.pdf.
59 You do not need to understand the queries/commands, just to make sure that you can do what’s requested.
60 if you run into difficulties doing that part of the assignment, please send email to William Wu, mailto:
61 yw4780@nyu.edu, explaining what the difficulties were. You will be helped.
62 2. You will need to create a relational implementation modifying relational04.architect, of the ER diagram
63 Assignment04.drawio satisfying the annotations in Assignment04.txt, using SQL Power Architect with
64 Crow’s feet notation and including the needed (but only the needed) annotations. Replace zk1 with your Net
65 ID and note what’s specified in Item 2a. You should follow exactly the ER diagram given and the example
66 of the University database we covered in class. Do not create better relational implementations if such are
67 possible by deviating from the ER diagram.
68 (a) Specify the following using SQL Power Architect and everything else that needs to be specified (if
69 anything) put in annotations by modifying Solution04.txt
70 • tables
71 • attributes, including for each that
72 – it must not be NULL (if applicable)
73 – it is a part of the primary key (if applicable)
74 – it is a part of a foreign key (if applicable)
75 It is not important for us what the domains of the attributes are, so use VARCHAR as the domain
76 for all attributes
77 • many-to-one mappings between tables (or one-to-one as applicable)
78 Please,
79 • Do not make any assumptions about the application beyond the specifications listed.
80 • Do not optimize your design, just follow the specification given.
81 • Do not use any other capabilities that SQL Power Architect provides; for example do not use
82 Index Properties or Remarks for Column Properties.
Typeset: 2021-10-02T17:04:00Z MD5: D5B78E17243B9F4BCD94B7CA4F8433C9
Page 3 of 3
83 • Anything that cannot be specified in your SQL Power Architect diagram, put as annotations in
84 Solution04.txt file, as described below.
85 • Do not put anything in the annotations that can be reflected in the SQL Power Architect imple86 mentation; make sure that the implementation reflects that.
87 (b) Modify the file Solution04.txt as specified in Item 1 of Section 3.
88 3 What to submit
89 Please upload 3 files, named exactly as specified and in the format exactly as specified.
90 1. Solution04.txt in the text format, by modifying the file in this assignment.
91 In that file
92 • Replace the Metadata with your information.
93 • State the required (and only the required) Annotations.
94 • State your responses by placing appropriate text after the item label as listed below (so your first item
95 will labeled “(a)”):
96 (a) State whether you have done what is requested in Item 1 of Section 2.2. If your answer is “Yes”,
97 just state that.
98 If not, explain why.
99 2. A file with a diagram in the native format as produced by SQL Power Architect. The file should be called
100 Relational04.architect.
101 3. A file with a PDF version of the diagram you have produced. The file should be called Relational04.pdf.
102 A An SQL Power Architect bug: Please read
103 SQL Power Architect sometimes produces inconsistent diagrams such as our R-S diagram in Figure 1. In R-S,
104 attribute A in S can be NULL but the left side of the line between R and S has a stroke | indicating that the
105 binary many-to-one mapping from S to R is total. Only R1-S1 and R2-S2 are correct. So make sure that your
106 diagram is correct, fixing what SQL Power Architect produces, if necessary. This is easy to do by modifying
107 Relationship Properties.
Figure 1: Read the text of Appendix A
Typeset: 2021-10-02T17:04:00Z MD5: D5B78E17243B9F4BCD94B7CA4F8433C9
CSCI-GA-2433 Homework 5
2.1 Description
53 There is one assignment: To create a relational implementation of an ER diagram.
54 2.2 Assignments
55 1. You will need to create a relational implementation modifying relational05.architect, of the ER diagram
56 Assignment05.drawio satisfying the annotations in Assignment05.txt, using SQL Power Architect with
57 Crow’s feet notation and including the needed (but only the needed) annotations. Replace zk1 with your Net
58 ID and note what’s specified in Item 1a. You should follow exactly the ER diagram given and the example
59 of the University database we covered in class. Do not create better relational implementations if such are
60 possible by deviating from the ER diagram.
61 (a) Specify the following using SQL Power Architect and everything else that needs to be specified (if
62 anything) put in annotations by modifying Solution05.txt
63 • tables
64 • attributes, including for each that
65 – it must not be NULL (if applicable)
66 – it is a part of the primary key (if applicable)
67 – it is a part of a foreign key (if applicable)
68 It is not important for us what the domains of the attributes are, so use VARCHAR as the domain
69 for all attributes
70 • many-to-one mappings between tables (or one-to-one as applicable)
71 Please,
72 • Do not make any assumptions about the application beyond the specifications listed.
73 • Do not optimize your design, just follow the specification given.
74 • Do not use any other capabilities that SQL Power Architect provides; for example do not use
75 Index Properties or Remarks for Column Properties.
76 • Anything that cannot be specified in your SQL Power Architect diagram, put as annotations in
77 Solution05.txt file, as described below.
78 • Do not put anything in the annotations that can be reflected in the SQL Power Architect imple79 mentation; make sure that the implementation reflects that.
80 (b) Modify the file Solution05.txt as specified in Item 1 of Section 3.
Typeset: 2021-10-16T12:46:53Z MD5: A6B2C863591EF2C53A73F690C0C66FAF
Page 3 of 3
81 3 What to submit
82 Please upload 3 files, named exactly as specified and in the format exactly as specified.
83 1. Solution05.txt in the text format, by modifying the file in this assignment.
84 In that file
85 • Replace the Metadata with your information.
86 • State the required (and only the required) Annotations.
87 2. A file with a diagram in the native format as produced by SQL Power Architect. The file should be called
88 Relational05.architect.
89 3. A file with a PDF version of the diagram you have produced. The file should be called Relational05.pdf.
90 A An SQL Power Architect bug: Please read
91 SQL Power Architect sometimes produces inconsistent diagrams such as our R-S diagram in Figure 1. In R-S,
92 attribute A in S can be NULL but the left side of the line between R and S has a stroke | indicating that the
93 binary many-to-one mapping from S to R is total. Only R1-S1 and R2-S2 are correct. So make sure that your
94 diagram is correct, fixing what SQL Power Architect produces, if necessary. This is easy to do by modifying
95 Relationship Properties.
Figure 1: Read the text of Appendix A
Typeset: 2021-10-16T12:46:53Z MD5: A6B2C863591EF2C53A73F690C0C66FAF
CSCI-GA-2433 Homework 6
2.1 Description
61 This is the first of two assignments dealing with SQL. Both assignments will use the same (or almost
62 the same) small database. This first assignment is simpler than the second one as this is the first
63 time you are actually writing and executing Oracle queries.
64 2.2 Assignments
65 1. (a) You are already supposed to know how to run SQL queries/commands on the Oracle
66 systems at CIMS. You were asked to familiarize yourself with and follow the instructions
67 in How_To_Use_Oracle_At_CIMS.pdf.
68 You were asked to do that earlier in order to save you time while working on this
69 homework.
70 So it is assumed that you know how to do that.
Typeset: 2021-10-24T23:07:53Z MD5:
Page 3 of 4
71 (b) Look at the files ER06.drawio and relational06.architect. They will help
72 you understand the database schema defined in the files script06.sql and
73 dataSetupScript06.sql. These files fully specify the application.
74 (c) Read script06.sql and dataSetupScript06.sql carefully. These scripts both define
75 and create the sample database and serves as the placeholder for putting in your solutions.
76 Look carefully over ANSWER0 there. It shows you how to insert a result of a query into
77 an empty table. It also uses the temp table TEMP0, just to demonstrate the usage of
78 temp tables.
79 (d) Input your queries into script06.sql after doing what is requested in Item 1e of Sec80 tion 2.2.
81 Please note which operations to use. Use only the operations that were introduced
82 in Unit 4 and the CREATE TABLE … AS …, used in the example of ANSWER0 in the
83 script. So base your queries on the operations of selection, projection, cartesian product,
84 minus, union, and intersection, with renaming and creation of new tables: creation, as
85 shown in the script. Do not, e.g., use JOINs of any kind. To reiterate: A solution that
86 used other operations (including those covered in Unit 5) will not be acceptable
87 In addition, use DISTINCT and ORDER BY as described below. If the output is to be
88 sorted in a different way, use an appropriate variant of ASC and DESC (ascending and
89 descending) and list the sorting instructions in the appropriate order,
90 For each query, unless something else is required by the query make sure to
91 i. Remove duplicates from the answer (unless requested otherwise); that’s what DIS92 TINCT does
93 ii. Sort the result in ascending order (unless requested otherwise); that’s what ORDER
94 BY does
95 This is extremely important to make the grading more manageable.
96 So, for example, assuming that you are going to select a and b and rename b as c, you
97 should actually explicitly use:
98 SELECT DISTINCT a, b AS c
99 …
100 ORDER BY a ASC, c ASC;
101 Do not rely the on default removal of duplicates and sorting order. Add the DISTINCT
102 and ORDER BY instructions even if you think that they are not necessary.
103 You may use temporary tables. If you choose to do that, use tables TEMP1, TEMP2,
104 . . . , TEMP20.
105 (e) Replace “zk1” in script06.sql with your NetID.
106 (f) Do not remove the existing sample query.
Typeset: 2021-10-24T23:07:53Z MD5:
Page 4 of 4
107 (g) Execute script06.sql, which internally calls dataSetupScript06.sql and produces a
108 spool file spool06.txt. The spool file must only include the details of the queries from
109 script06.sql. The spool file created will be a part of the submission.
110 Do not be concerned that there are more placeholder ANSWERs, than the queries that
111 you are supposed to produce.
112 For reference, spool06.txt corresponding to the given script06.sql (with one sample
113 query) is enclosed.
114 The requested queries are listed below. Your answers must work for every
115 instance of the database and not only for the specific instance provided. The
116 tables are named AnswerX, where “X” stands for the item number below. So, as the first
117 item is item number 1, the first table is Answer1.
118 1. Produce table AnswerX(RegNumber, TIN) that lists all the companies, sorted ascend119 ing by RegNumber first and descending by TIN. Note that the order of the attributes
120 is not the same in both places.
121 2. Produce table AnswerX(RegNumber, TIN) that lists all the big companies that work
122 in the ‘IT’ domain and are managed by people with familyName ‘Garcia’.
123 3. Produce table AnswerX(helperTIN, helpedTIN) that lists all the companies that help
124 each other. Note that (TIN1, TIN2) and (TIN2, TIN1) are not duplicates if TIN1 and
125 TIN2 are not the same.
126 4. Produce table AnswerX(RegNumber, TIN) that lists all the companies that work
127 in the ‘IT’ domain, and are helped by both companyOne with TIN ‘59515298’, and
128 companyTwo with TIN ‘51251930’.
129 5. Produce table AnswerX(RegNumber, TIN) that lists all the companies (big or small)
130 that do not help any company.
131 6. Produce table AnswerX(RegNumber, TIN) that lists all the companies, sorted as132 cending by RegNumber first and ascending by TIN, that do not work in the ‘IT’
133 industry.
134 7. Produce table AnswerX(RegNumber, TIN) that lists all the small companies that work
135 in ‘banking’ domain that work in the ‘IT’ domain, and are helped by small companies
136 in ‘IT’ domain.
137 2.3 What to submit
138 Please upload 2 files, named exactly as specified and in the format exactly as specified.
139 1. script06.sql, the script with your answers
140 2. spool06.txt, the resulting spool file
Typeset: 2021-10-24T23:07:53Z MD5:
CSCI-GA-2433 Homework 7
This is the second of two assignments dealing with SQL. Both assignments use the same small database with very
56 similar instances.
57 2.2 Assignments
58 1. (a) You are already supposed to know how to run SQL queries/commands on the Oracle sys59 tems at CIMS. You were asked to familiarize yourself with and follow the instructions in
60 How_To_Use_Oracle_At_CIMS.pdf.
61 You were asked to do that earlier in order to save you time while working on this homework.
62 So it is assumed that you know how to do that.
63 (b) Look at the files ER07.drawio and relational07.architect. They will help you understand the
64 database schema defined in the files script07.sql and dataSetupScript07.sql. These files fully
65 specify the application.
66 (c) Read script07.sql and dataSetupScript07.sql carefully. They both define and create the sample
67 database and script07.sql serves as the placeholder for putting in your solutions.
68 Look carefully over ANSWER0 there. It shows you how to insert a result of a query into an empty
69 table. It also uses the temp table TEMP0, just to demonstrate the usage of temp tables.
70 (d) Input your queries into script07.sql after doing what is requested in Item 1e of Section 2.2.
71 For each query, unless something else is required by the query make sure to
72 i. Remove duplicates from the answer (unless requested otherwise); that’s what DISTINCT does
73 ii. Sort the result in ascending order (unless requested otherwise); that’s what ORDER BY does
74 This is extremely important to make the grading more manageable.
75 So, for example, assuming that you are going to select a and b and rename b as c, you should actually
76 explicitly use:
77 SELECT DISTINCT a, b AS c
78 …
79 ORDER BY a ASC, c ASC;
80 Do not rely the on default removal of duplicates and sorting order.
81 You may use temporary tables. If you choose to do that, use tables TEMP1, TEMP2, . . . , TEMP20.
82 (e) Replace “zk1” in script07.sql with your NetID.
83 (f) Do not remove the existing sample query.
Typeset: 2021-11-04T17:35:03Z MD5:
Page 3 of 4
84 (g) Notice that the script07.sql internally executes dataSetupScript07.sql followed by the queries
85 you write in script07.sql, and produces a spool file spool07.txt that contains just the details of the
86 queries specified in the script script07.sql. The spool should only contain the details while running
87 script07.sql and not the data setup script. The spool file created will be a part of the submission.
88 Do not be concerned that there are more placeholder ANSWERs that the queries that you are supposed
89 to produce.
90 For reference, spool07.txt corresponding to the given script07.sql (with one sample query) is
91 enclosed.
92 The requested queries are listed below. Your answers must work for every instance of
93 the database and not only for the specific instance provided. Do not use subqueries to
94 produce your output. The tables are named AnswerX, where “X” stands for the item number below.
95 So, as the first item is item number 1, the first table is Answer1.
96 1. Produce table AnswerX(RegNumber, TIN) that lists all the big companies that do not have a
97 manager.
98 2. Produce table AnswerX(TIN, SalaryRatio) that lists the TIN of the big companies that spend more
99 than 10 percent of their value on manager salary.
100 3. Produce table AnswerX(TIN) that lists the TIN of companies that work in at least 3 domains and
101 have a TIN greater than or equal to 20000000.
102 Your answer should work if the question were phrased with 500 instead of 3 if you replace 3 by 500
103 in your answer.
104 4. Produce table AnswerX(TIN) that lists the companies that work at least in all the domains that
105 company with TIN ‘99448276’ works in.
106 5. Write and execute a query to delete from table Company the company with TIN ‘74939103’. Please
107 do what’s requested in Item 1h of Section 2.2.
108 6. Company ‘95499546’ has hired a new manager with PersonalName ‘Mickey’, FamilyName ‘Aldrin’with
109 salary 100000. Write and execute a query to add/change the name of the store’s manager in our
110 records wherever necessary.
111 Please do what’s requested in Item 1h of Section 2.2.
112 7. Company ‘35983220’ has made huge mistakes in the recent past, and lost a lot of its value. As
113 a result, they have to fire their current manager. They would now be a small company. Write
114 and execute a query to remove the company from big companies, and make the company a small
115 company.
116 Please do what’s requested in Item 1h of Section 2.2.
117 8. Write and execute a query to create a record of company with TIN ‘74939103’ helping company
118 with TIN ‘90309251’.
119 Please do what’s requested in Item 1h of Section 2.2.
120 9. MySQL does not have a very useful operator we studied: MINUS. Review the definition of MINUS
121 from Unit 05.
122 Do not use MINUS for this question. Produce table AnswerX(A, B), which is R MINUS S. Use
123 LEFT OUTER JOIN in your answer. Tables R and S are defined in the script.
124 (h) Examine the database and fill out text07.txt. For each “AnswerX” (of course replacing “X” with the
125 correct value), supply the information requested between the two square brackets, in [ ]. The form is
126 clear but to elaborate it is asking you to specify which tables (if any) were changed and why. You may
127 get an error message pertaining to this question. If you do get it,
128 • copy the error into text07.txt, and
Typeset: 2021-11-04T17:35:03Z MD5:
Page 4 of 4
129 • explain in your own words what the error was, and do not just copy the message that Oracle gave
130 you, though you can also quote it. You may consult the Web, if you like.
131 If you do not get an error, state so.
132 2.3 What to submit
133 Please upload 3 files, named exactly as specified and in the format exactly as specified.
134 1. script07.sql, the script with your answers
135 2. spool07.txt, the resulting spool file
136 3. text07.txt, the file requested in Item 1h of Section 2.2
Typeset: 2021-11-04T17:35:03Z MD5:
