关键词 > COMP3311
COMP3311 24T2 Assignment 1 Database Systems Example Test Cases
发布时间：2024-06-26
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
COMP3311 24T2
Assignment 1
Database Systems
Example Test Cases
Last updated: Tuesday 18th June 12:58am
Most recent changes are shown in red ... older changes are shown in brown.
[Assignment Spec] [Database Design] [Examples] [Testing]
The following will give some sample inputs and outputs that you can use to estimate the correctness of your views and functions. At present it just shows output from a few simple queries.
These examples are by no means exhaustive, and more cases will be used in the auto-marking; it is up to you to perform comprehensive checking of your solution.
Examples for RugDB
-- Expected output for assignment views
ass1=# select * from q1 order by province;
province | nfactories
----------------------------+------------
East Azerbaijan | 39
Fars | 39
Gilan | 36
Ilam | 31
Isfahan | 21
Kohgiluyeh and Boyer-Ahmad | 30
Markazi | 17
Mazandaran | 44
Semnan | 12
Zanjan | 28
(10 rows)
ass1=# select * from q2 order by style;
style | knot_length_diff
-----------------+------------------
Ghashghai_TypeC | 11
Shiraz_TypeD | 11
(2 rows)
ass1=# select * from q3 order by style;
style | lo_knot_length | hi_knot_length | min_kno
------------------------+----------------+----------------+-------
Abadeh_TypeC | 0 | 0 |
Arak_TypeB | 6 | 8.2 |
Birjand_TypeA | 6 | 13.4 |
Boteh_TypeA | 4.7 | 5.4 |
Ghashghai_TypeC | 8 | 41 |
Gol Henai_TypeC | 6.4 | 10.1 |
Gul Hannai_TypeA | 4.4 | 5.1 |
Hamadan_TypeA | 4.2 | 5.5 |
Hamedan_TypeD | 5.1 | 7.3 |
Heriz_TypeA | 0 | 0 |
Heriz_TypeD | 8.5 | 9.2 |
Kashmar_TypeB | 0 | 0 |
Kilim | 3.5 | 7.2 |
Malayer_TypeB | 6.1 | 13.2 |
Qashqai_TypeA | 6.9 | 12.3 |
Qom_TypeB | 6 | 8.3 |
Qom_TypeD | 0 | 0.5 |
Sarough_TypeD | 9.7 | 11.3 |
Shah Abbasi_TypeC | 10 | 13.2 |
Shiraz_TypeB | 5.2 | 9.4 |
Shiraz_TypeD | 7.7 | 18 |
Sultanabad-Mahal_TypeB | 5.3 | 6.3 |
Toranj_TypeA | 7 | 7.3 |
Touserkan_TypeC | 7.7 | 11.1 |
Touserkan_TypeD | 10 | 11.3 |
Yazd_TypeC | 5 | 8.2 |
Zanjan_TypeB | 0 | 0 |
(27 rows)
ass1=# select * from q4 order by factory;
factory | rating
-----------------------------+--------
Tribe Kin Tradition Carpets | 9.2
(1 row)
ass1=# select * from q5('lan') order by rug;
rug | size_and_stoper | total_knots
--------------------------+-----------------+-------------
Dalan Rug | 500sf faux | 1250000
Gerd Kulan | 355sf faux | 887500
Gilan | 440sf faux | 1100000
Gilan Jasper Rug | 500sf faux | 1250000
Gondeh Golan Olya (Rug) | 375sf faux | 937500
Gondeh Golan Safli (Rug) | 330sf faux | 825000
Jamush Olan Sofla Rug | 375sf faux | 937500
Jamush Olan Ulya Rug | 375sf faux | 937500
Kalani Badagh | 500sf faux | 612500
Kani Khalilan | 440sf faux | 1100000
Kani Kiselan | 440sf faux | 1100000
Sarshilaneh Rug | 375sf faux | 937500
Tazehabad Sardalan Rug | 355sf leather | 434875
(13 rows)
ass1=# select * from q5('rug') order by rug;
rug | size_and_stoper | total_knots
------------------------+-----------------+-------------
Nirugah Abi Biareh Rug | 330sf leather | 825000
(1 row)
ass1=# select * from q5('[0-9]');
rug | size_and_stoper | total_
----------------------------------------+-----------------+-------
Chah Shomare 1 Azan Rug | 375sf faux | 4
Esteghah 53 Ghadirabad Station | 440sf faux | 110
Mini Full 21 Dezh Suleyman Rug | 330sf leather | 82
Ti 25 Pashid Hossein Shahram Far (Rug) | 330sf leather | 82
(4 rows)
ass1=# select * from q6('fa') order by province;
province | first | nrugs | rating
----------+-------+-------+--------
Fars | 2000 | 132 | 7.4
Isfahan | 2017 | 95 | 7.4
(2 rows)
ass1=# select * from q6('an') order by province;
province | first | nrugs | rating
----------------------------+-------+-------+--------
East Azerbaijan | 1994 | 106 | 7.3
Gilan | 2000 | 162 | 7.6
Isfahan | 2017 | 95 | 7.4
Kohgiluyeh and Boyer-Ahmad | 2000 | 93 | 7.2
Mazandaran | 2000 | 154 | 7.6
Semnan | 2012 | 75 | 7.4
Zanjan | 2001 | 71 | 7.4
(7 rows)
ass1=# select * from q6('AND');
province | first | nrugs | rating
----------------------------+-------+-------+--------
Kohgiluyeh and Boyer-Ahmad | 2000 | 93 | 7.2
Mazandaran | 2000 | 154 | 7.6
(2 rows)
ass1=# select * from q7(1);
q7
-----------------
No such rug (1)
(1 row)
ass1=# select * from q7(456);
q7
-------------------------
"Badengan Sofli Rug" +
no materials recorded
(1 row)
ass1=# select * from q7(1327);
q7
--------------------------------------------
"Yagush" +
contains: +
Assam Silk (weft) +
Cotton-Polyester-Neoprene Blend (weft)+
Maheshwari Silk (weft) +
Medium Staple Cotton (warp) +
Organic Cotton (weft) +
Saxon Merino Wool (warp) +
Silk-Polyester-Cupro Blend (warp) +
Silk-Polyester-Viscose Blend (weft) +
Silk-Polyester-Wool Blend (warp) +
Supima Cotton (weft) +
Wool-Acrylic-Polyester Blend (weft)
(1 row)
ass1=# select * from q7(891);
q7
---------------------------------------------------------
"Lashtnesha Azura Rug" +
contains: +
Bourette Silk (weft) +
Luxury Cashmere (High-end Designer Cashmere) (weft)+
Maheshwari Silk (weft) +
Organic Cotton (weft) +
Romney Wool (weft) +
Saxon Merino Wool (warp) +
Sea Island Cotton (weft) +
Supima Cotton (weft)
(1 row)
ass1=# select * from q8('zard') order by rug;
rug | factory
--------------------------------+---------------------------------
Ben Zard Olya Rug | Roamer Clan Jovial Rugs+Urmia Woo
Ben Zard Sofli Rug | Roaming Kin Silk Sensation
Mazrae Kani Zard | Clan Royal Masterful Rugs
Piz Zard Bahram Beygi Olya Rug | Shiraz Silk Dynasty
(4 rows)
ass1=# select * from q8('kani') order by rug;
rug | factory
---------------------------------------+--------------------------
Kani Ashkut | Masterful Silk Rugs
Kani Bagh | Malayer Masterpieces
Kani Hengeh Rug | Kermanshah Family Weavers
Kani Kabood Rug | Shahr-e Kord Family Weave
Kani Khalilan | Dynasty Clan Fine Art Car
Kani Kileh | Semnan Superior Weaves
Kani Kiselan | Zahedan Silk Elegance
Kani Molla | Hamedan Heritage Carpets
Kani Nobateh | Roamer Clan Jovial Rugs
Kani Sayyed Morad Rug | Dynasty Clan Fine Art Car
Mazrae Kani Zard | Clan Royal Masterful Rugs
Pirreh Kani | Elegant Artistry Carpets
Shahrak Golkhanei Mah Laleh Saro Kani | Roaming Kin Shimmering Rug
(13 rows)
ass1=# select * from q8('aaa') order by rug;
rug | factory | piles
-----+---------+-------
(0 rows)
ass1=# select * from q8('aa') order by rug;
rug | factory
--------------------------------+---------------------------------
Dam Tang Sarnaa Rug | Masterpiece Silk Weaves
Dareh Cheli Darshaahi Rug | Beautifully Crafted Elegance
Darshaahi Rug | Roamer Clan Jovial Rugs
Eslam Abad Darshaahi Rug | Kin Folk Grand Carpets
Khas Abad / Khasban/ Kiaan Rug | Tribe Dynasty Masterful Rugs
Khomeini Abad Darshaahi Rug | Roamer Clan Jovial Rugs
Khraapa (Rug) | Abyaneh Artisanal Rugs
Morghdari Dasht Bazaar Rug | Karaj Klassic Weaves
Rezaabad Boukan Rug | Tribe Clan Artisanal Rugs
Rezaabad Rug | Silk Sensation Splendor
Rudbar Kiaan Rug | Masjed Soleyman Silk Sensation
Saadat Abad Lishetr Rug | Yazd Artisan Carpets
Saadat Abad Rug | Isfahan Wool Artisans+Roamer Cla
(13 rows)
-- A very long tuple displays best in "extended" mode
-- In "extended" mode, each field is displayed on a separate line
-- This particular beer has a large number of collaborating brewer
ass1=# \x
Expanded display is on.
ass1=# select * from q8('arak') order by rug;
-[ RECORD 1 ]-----------------------------------------------------
rug | Arak
factory | Artistic Grandeur Rugs+Birjand Beautiful Carpets+Luxurio
piles | Cotton-Polyester-Rayon-Nylon Blend,Dartmoor Wool,Turkish
-[ RECORD 2 ]-----------------------------------------------------
rug | Arak Yasmine Rug
factory | Roamer Clan Jovial Rugs
piles | Wool-Rayon Blend
-[ RECORD 3 ]-----------------------------------------------------
rug | Bagh Nargoon Sarak Rug
factory | King's Silk Passage
piles | no piles recorded
-[ RECORD 4 ]-----------------------------------------------------
rug | Darak Rug
factory | Luxurious Artisan Rugs
piles | no piles recorded
ass1=# \x
Expanded display is off.
ass1=# select * from q9(1);
factory | collaborator
---------------------+--------------
No such factory (1) | none
(1 row)
ass1=# select * from q9(11);
factory | collaborator
----------------------+--------------
Ahvaz Authentic Rugs | none
(1 row)
ass1=# select * from q9(111);
factory | collaborator
-------------------------+-----------------------
Kin Clan Legacy Carpets | Kermani Handmade Rugs
(1 row)
ass1=# select * from q9(188);
factory | collaborator
-------------------------+-------------------------------
Roamer Clan Jovial Rugs | Artisanal Royal Rugs
| Dynasty Clan Fine Art Carpets
| Gorgan Silk Elegance
| Great Artisanal Weaves
| Isfahan Wool Artisans
| Kermanshah Silk Masters
| Shahr-e Kord Family Weavers
| Urmia Wool Creations
| Varamin Velvety Carpets
| Yazd Yarn & Looms Factory
(10 rows)
ass1=# select * from q9(118);
factory | collaborator
----------------------------+-----------------------------
Kin Folk Nonpareil Carpets | Artisanal Luxe Rugs
| Gorgan Silk Elegance
| Mashhad Silk Dynasty
| Masterful Silk Weaves
| Quaint Luxe Rugs
| Rasht Royal Rugs
| Royal Crafted Splendor
| Tribe Kin Tradition Carpets
(8 rows)
ass1=# select * from q9(36);
factory | collaborator
---------------------------+------------------------
Birjand Beautiful Carpets | Artistic Grandeur Rugs
| Luxurious Artisan Rugs
| Qom Artisan Carpets
| Quality Silk Splendor
| Rasht Silk Creations
| Rasht Silk Elegance
| Royal Masterful Rugs
(7 rows)
ass1=# select * from q9(26);
factory | collaborator
------------------------+---------------------------
Artistic Grandeur Rugs | Birjand Beautiful Carpets
| Luxurious Artisan Rugs
| Quality Silk Splendor
| Rasht Silk Creations
| Rasht Silk Elegance
| Royal Masterful Rugs
