| COMP3311 24T2 |
Assignment 1 Example Test Cases |
Database Systems |
[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.
-- 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_knot_length | max_knot_length
------------------------+----------------+----------------+-----------------+-----------------
Abadeh_TypeC | 0 | 0 | 0.1 | 0.5
Arak_TypeB | 6 | 8.2 | 5 | 8
Birjand_TypeA | 6 | 13.4 | 5 | 13
Boteh_TypeA | 4.7 | 5.4 | 4 | 5
Ghashghai_TypeC | 8 | 41 | 9 | 20
Gol Henai_TypeC | 6.4 | 10.1 | 5 | 10
Gul Hannai_TypeA | 4.4 | 5.1 | 3 | 5
Hamadan_TypeA | 4.2 | 5.5 | 4.5 | 5.9
Hamedan_TypeD | 5.1 | 7.3 | 4 | 7
Heriz_TypeA | 0 | 0 | 0.1 | 0.5
Heriz_TypeD | 8.5 | 9.2 | 8 | 9
Kashmar_TypeB | 0 | 0 | 0.1 | 0.5
Kilim | 3.5 | 7.2 | 3 | 7
Malayer_TypeB | 6.1 | 13.2 | 5 | 13
Qashqai_TypeA | 6.9 | 12.3 | 6 | 12
Qom_TypeB | 6 | 8.3 | 5 | 8
Qom_TypeD | 0 | 0.5 | 0.1 | 0.5
Sarough_TypeD | 9.7 | 11.3 | 9 | 11
Shah Abbasi_TypeC | 10 | 13.2 | 9 | 13
Shiraz_TypeB | 5.2 | 9.4 | 4 | 9
Shiraz_TypeD | 7.7 | 18 | 8 | 19
Sultanabad-Mahal_TypeB | 5.3 | 6.3 | 4 | 6
Toranj_TypeA | 7 | 7.3 | 6 | 7
Touserkan_TypeC | 7.7 | 11.1 | 7 | 11
Touserkan_TypeD | 10 | 11.3 | 9 | 11
Yazd_TypeC | 5 | 8.2 | 4 | 8
Zanjan_TypeB | 0 | 0 | 0.1 | 0.5
(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_knots
----------------------------------------+-----------------+-------------
Chah Shomare 1 Azan Rug | 375sf faux | 459375
Esteghah 53 Ghadirabad Station | 440sf faux | 1100000
Mini Full 21 Dezh Suleyman Rug | 330sf leather | 825000
Ti 25 Pashid Hossein Shahram Far (Rug) | 330sf leather | 825000
(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 | piles
--------------------------------+----------------------------------------------+---------------------------
Ben Zard Olya Rug | Roamer Clan Jovial Rugs+Urmia Wool Creations | Anaphe Silk,Baby Cashmere
Ben Zard Sofli Rug | Roaming Kin Silk Sensation | no piles recorded
Mazrae Kani Zard | Clan Royal Masterful Rugs | no piles recorded
Piz Zard Bahram Beygi Olya Rug | Shiraz Silk Dynasty | no piles recorded
(4 rows)
ass1=# select * from q8('kani') order by rug;
rug | factory | piles
---------------------------------------+-------------------------------+-----------------------------------------------------------
Kani Ashkut | Masterful Silk Rugs | no piles recorded
Kani Bagh | Malayer Masterpieces | no piles recorded
Kani Hengeh Rug | Kermanshah Family Weavers | no piles recorded
Kani Kabood Rug | Shahr-e Kord Family Weavers | Gotland Wool,Nepali Cashmere,Turkish Cashmere
Kani Khalilan | Dynasty Clan Fine Art Carpets | Gotland Wool,Kosa Silk
Kani Kileh | Semnan Superior Weaves | no piles recorded
Kani Kiselan | Zahedan Silk Elegance | Matka Silk,Nepali Cashmere,Turkish Cashmere
Kani Molla | Hamedan Heritage Carpets | no piles recorded
Kani Nobateh | Roamer Clan Jovial Rugs | Gotland Wool,New Zealand Cashmere,Turkish Cashmere
Kani Sayyed Morad Rug | Dynasty Clan Fine Art Carpets | Doupion Silk,Intarsia Cashmere,Kota Silk,Turkish Cashmere
Mazrae Kani Zard | Clan Royal Masterful Rugs | no piles recorded
Pirreh Kani | Elegant Artistry Carpets | no piles recorded
Shahrak Golkhanei Mah Laleh Saro Kani | Roaming Kin Shimmering Rugs | no piles recorded
(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 | piles
--------------------------------+-----------------------------------------------+------------------------------------------------------------------------
Dam Tang Sarnaa Rug | Masterpiece Silk Weaves | no piles recorded
Dareh Cheli Darshaahi Rug | Beautifully Crafted Elegance | no piles recorded
Darshaahi Rug | Roamer Clan Jovial Rugs | no piles recorded
Eslam Abad Darshaahi Rug | Kin Folk Grand Carpets | Nepali Cashmere,New Zealand Cashmere,Texel Wool
Khas Abad / Khasban/ Kiaan Rug | Tribe Dynasty Masterful Rugs | no piles recorded
Khomeini Abad Darshaahi Rug | Roamer Clan Jovial Rugs | Baby Cashmere,Texel Wool,Turkish Cashmere
Khraapa (Rug) | Abyaneh Artisanal Rugs | no piles recorded
Morghdari Dasht Bazaar Rug | Karaj Klassic Weaves | no piles recorded
Rezaabad Boukan Rug | Tribe Clan Artisanal Rugs | no piles recorded
Rezaabad Rug | Silk Sensation Splendor | no piles recorded
Rudbar Kiaan Rug | Masjed Soleyman Silk Sensation | no piles recorded
Saadat Abad Lishetr Rug | Yazd Artisan Carpets | no piles recorded
Saadat Abad Rug | Isfahan Wool Artisans+Roamer Clan Jovial Rugs | Jamdani Silk,Merino Wool,Turkish Cashmere,Wool-Polyester-Spandex Blend
(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 breweries
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+Luxurious Artisan Rugs+Quality Silk Splendor+Rasht Silk Creations+Rasht Silk Elegance+Royal Masterful Rugs
piles | Cotton-Polyester-Rayon-Nylon Blend,Dartmoor Wool,Turkish Cashmere,Twill Silk,Wool-Acrylic Blend
-[ 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
(6 rows)